package org.tgcloud.group.web.controller.manager;

import com.alibaba.fastjson.JSON;
import com.google.common.collect.Lists;
import io.swagger.annotations.*;
import org.apache.commons.io.FileUtils;
import org.apache.http.client.methods.CloseableHttpResponse;
import org.apache.http.client.methods.HttpGet;
import org.apache.http.impl.client.CloseableHttpClient;
import org.apache.http.impl.client.HttpClients;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.scheduling.annotation.Async;
import org.springframework.web.bind.annotation.*;
import org.tgcloud.group.core.APPResultMessage;
import org.tgcloud.group.core.BeanUtil;
import org.tgcloud.group.core.DateUtil;
import org.tgcloud.group.core.ExportUtils.ExportStyleUtils;
import org.tgcloud.group.core.StringUtil;
import org.tgcloud.group.entity.MjProject;
import org.tgcloud.group.entity.SysLabelValue;
import org.tgcloud.group.repository.MjProjectMapper;
import org.tgcloud.group.repository.SysLabelValueMapper;
import org.tgcloud.group.service.manager.ProjectManageService;
import org.tgcloud.group.web.controller.dto.export.MediumOfflineAllExportDTO;
import org.tgcloud.group.web.controller.dto.export.MediumOfflineExportDTO;
import org.tgcloud.group.web.controller.dto.export.*;
import org.tgcloud.group.web.controller.dto.export.MediumOnlineExportDTO;
import org.tgcloud.group.web.controller.dto.home.HomeProjectMsg;
import org.tgcloud.group.web.controller.dto.medium.ProjectMediumListDTO;

import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletResponse;
import javax.swing.*;
import java.awt.*;
import java.awt.image.BufferedImage;
import java.io.*;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.net.URL;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.List;

/**
 * @author: zhy
 * @create: 2023-11-22 14:08
 * @description: 导出
 */
@Api(tags = "导出相关接口")
@RestController
@RequestMapping("/manager/pc/user")
@EnableAutoConfiguration
public class ExportManageController extends ManageAPPBaseController{

    @Autowired
    private ProjectManageService projectManageService;

    @Autowired
    private MjProjectMapper projectMapper;

    @Autowired
    private SysLabelValueMapper sysLabelValueMapper;

    @ApiOperation(value = "导出配包")
    @ApiImplicitParams({
    })
    @ApiResponses({
            @ApiResponse(code = 2000, message = "出现异常，及时通知后台")
    })
    @ResponseBody
    @PostMapping(value = "/doExportPackageOld")
    public void doExportFile(HttpServletResponse response,
                             @RequestParam int projectId,
                             @RequestParam int indexNum) throws Exception{
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/octet-stream;charset=UTF-8");
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMM");
        MjProject mjProject = projectMapper.selectByPrimaryKey(projectId);
        String excelName = mjProject.getBrands() + dateFormat.format(new Date()) + "配包方案";
        response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(excelName, "UTF-8") + ".xls");
        response.addHeader("filename", URLEncoder.encode(excelName, "UTF-8"));


        try {
            OutputStream out = response.getOutputStream();
            APPResultMessage successMessage = projectManageService.viewPackageManageProjectMsg(projectId,indexNum);

            List<ProjectMediumListDTO> list = successMessage.getResult().getList();
            List<MediumOnlineExportDTO> onlineExportList = new ArrayList<>();
            Map<Integer, MediumOfflineAllExportDTO> offlineMap = new HashMap<>();
            if (!BeanUtil.isNullList(list)) {
                for (int i = 0; i < list.size(); i++){
                    ProjectMediumListDTO dto = list.get(i);
                    if (dto.getLineType() == 1){
                        MediumOnlineExportDTO mediumOnlineExportDTO = new MediumOnlineExportDTO();
                        mediumOnlineExportDTO.setIndexNum(onlineExportList.size() + 1);
                        mediumOnlineExportDTO.setPlateName(dto.getMediumOnline().getPlateName());
                        mediumOnlineExportDTO.setUserTypeAndCreateType(dto.getMediumOnline().getUserTypeName() + " - " + dto.getMediumOnline().getCreateType());
                        mediumOnlineExportDTO.setFans(dto.getMediumOnline().getFans());
                        mediumOnlineExportDTO.setGoodPrice(dto.getMediumOnline().getGoodPrice());
                        mediumOnlineExportDTO.setRemark(dto.getMediumOnline().getRemark());
                        mediumOnlineExportDTO.setNum(dto.getNum());
                        mediumOnlineExportDTO.setTotalPrice(dto.getTotalPrice());
                        onlineExportList.add(mediumOnlineExportDTO);
                    }else if (dto.getLineType() == 2){
                        if (dto.getMediumOffline().getExcelShowTitleId() == 497){
                            String steadyContent = "";
                            MediumOfflineExportDTOByJC offlineDTO = new MediumOfflineExportDTOByJC();
                            offlineDTO.setId(dto.getLineId());
                            offlineDTO.setIndexNum(i + 1);
                            offlineDTO.setName(dto.getMediumOffline().getName());
                            offlineDTO.setPositionSpecs((StringUtil.isNotBlank(dto.getMediumOffline().getAddress()) ? dto.getMediumOffline().getAddress() : "") + " \n " + dto.getMediumOffline().getPositionSpecs());
                            offlineDTO.setPublicationSecondsAndBroadcastFrequency(dto.getMediumOffline().getPublicationSeconds() + "，" + dto.getMediumOffline().getBroadcastFrequency());
                            offlineDTO.setScreenNum(dto.getMediumOffline().getScreenNum());
                            offlineDTO.setGoodPrice(dto.getMediumOffline().getGoodPrice());
                            Integer num = dto.getNum();
                            if (dto.getMediumOffline().getPutInCycle().contains("月")){
                                num = num * 4;
                            }
                            offlineDTO.setNum(num);
                            offlineDTO.setTotalPrice(dto.getTotalPrice());
                            offlineDTO.setPic(dto.getMediumOffline().getPic());
                            if (StringUtil.isNotBlank(dto.getMediumOffline().getSteadyContent()) && !steadyContent.contains(dto.getMediumOffline().getSteadyContent())){
                                steadyContent = steadyContent + dto.getMediumOffline().getSteadyContent() + "\n";
                            }
                            if (offlineMap.containsKey(dto.getMediumOffline().getExcelShowTitleId())){
                                if (offlineMap.get(dto.getMediumOffline().getExcelShowTitleId()).getList() == null){
                                    List<MediumOfflineExportDTOByJC> mediumOfflineExportDTOList = new ArrayList<>();
                                    mediumOfflineExportDTOList.add(offlineDTO);
                                    offlineMap.get(dto.getMediumOffline().getExcelShowTitleId()).setList(mediumOfflineExportDTOList);
                                }else {
                                    boolean hasAdd = false;
                                    for (int z = 0;z<offlineMap.get(dto.getMediumOffline().getExcelShowTitleId()).getList().size();z++){
                                        MediumOfflineExportDTOByJC dtoZ = (MediumOfflineExportDTOByJC) offlineMap.get(dto.getMediumOffline().getExcelShowTitleId()).getList().get(z);
                                        if (dtoZ.getId() == offlineDTO.getId()){
                                            dtoZ.setNum(dtoZ.getNum() + offlineDTO.getNum());
                                            dtoZ.setTotalPrice(dtoZ.getTotalPrice().add(offlineDTO.getTotalPrice()));
                                            hasAdd = true;
                                        }
                                    }
                                    if (!hasAdd){
                                        offlineMap.get(dto.getMediumOffline().getExcelShowTitleId()).getList().add(offlineDTO);
                                    }
                                }
                                if (!steadyContent.contains(offlineMap.get(dto.getMediumOffline().getExcelShowTitleId()).getSteadyContent())){
                                    offlineMap.get(dto.getMediumOffline().getExcelShowTitleId()).setSteadyContent(steadyContent + offlineMap.get(dto.getMediumOffline().getExcelShowTitleId()).getSteadyContent());
                                }
                            }else {
                                MediumOfflineAllExportDTO mediumOfflineAllExportDTO = new MediumOfflineAllExportDTO();
                                mediumOfflineAllExportDTO.setMediumOfflineType(dto.getOfflineMediumType());
                                mediumOfflineAllExportDTO.setExcelShowTitleId(dto.getMediumOffline().getExcelShowTitleId());
                                mediumOfflineAllExportDTO.setMediumOfflineTypeName(dto.getMediumOffline().getOfflineTypeName());
                                mediumOfflineAllExportDTO.setObjectClass(MediumOfflineExportDTOByJC.class);
                                mediumOfflineAllExportDTO.setSteadyContent(steadyContent);
                                mediumOfflineAllExportDTO.setExcelShowTitle(dto.getMediumOffline().getExcelShowTitle());
                                List<MediumOfflineExportDTOByJC> mediumOfflineExportDTOList = new ArrayList<>();
                                mediumOfflineExportDTOList.add(offlineDTO);
                                mediumOfflineAllExportDTO.setList(mediumOfflineExportDTOList);
                                mediumOfflineAllExportDTO.setObjectClass(MediumOfflineExportDTOByJC.class);
                                offlineMap.put(dto.getMediumOffline().getExcelShowTitleId(), mediumOfflineAllExportDTO);
                            }
                        }else if (dto.getMediumOffline().getExcelShowTitleId() == 496){
                            String steadyContent = "";
                            MediumOfflineExportDTOByGT offlineDTO = new MediumOfflineExportDTOByGT();
                            offlineDTO.setId(dto.getLineId());
                            offlineDTO.setIndexNum(i + 1);
                            offlineDTO.setName(dto.getMediumOffline().getName());
                            offlineDTO.setPositionSpecs((StringUtil.isNotBlank(dto.getMediumOffline().getAddress()) ? dto.getMediumOffline().getAddress() : "") + " \n " + dto.getMediumOffline().getPositionSpecs());
                            offlineDTO.setPublicationSecondsAndBroadcastFrequency(dto.getMediumOffline().getPublicationSeconds() + "，" + dto.getMediumOffline().getBroadcastFrequency());
                            offlineDTO.setScreenNum(dto.getMediumOffline().getScreenNum());
                            offlineDTO.setGoodPrice(dto.getMediumOffline().getGoodPrice());
                            Integer num = dto.getNum();
                            if (dto.getMediumOffline().getPutInCycle().contains("月")){
                                num = num * 4;
                            }
                            offlineDTO.setNum(num);
                            offlineDTO.setTotalPrice(dto.getTotalPrice());
                            offlineDTO.setPic(dto.getMediumOffline().getPic());
                            if (StringUtil.isNotBlank(dto.getMediumOffline().getSteadyContent()) && !steadyContent.contains(dto.getMediumOffline().getSteadyContent())){
                                steadyContent = steadyContent + dto.getMediumOffline().getSteadyContent() + "\n";
                            }
                            if (offlineMap.containsKey(dto.getMediumOffline().getExcelShowTitleId())){
                                if (offlineMap.get(dto.getMediumOffline().getExcelShowTitleId()).getList() == null){
                                    List<MediumOfflineExportDTOByGT> mediumOfflineExportDTOList = new ArrayList<>();
                                    mediumOfflineExportDTOList.add(offlineDTO);
                                    offlineMap.get(dto.getMediumOffline().getExcelShowTitleId()).setList(mediumOfflineExportDTOList);
                                }else {
                                    boolean hasAdd = false;
                                    for (int z = 0;z<offlineMap.get(dto.getMediumOffline().getExcelShowTitleId()).getList().size();z++){
                                        MediumOfflineExportDTOByGT dtoZ = (MediumOfflineExportDTOByGT) offlineMap.get(dto.getMediumOffline().getExcelShowTitleId()).getList().get(z);
                                        if (dtoZ.getId() == offlineDTO.getId()){
                                            dtoZ.setNum(dtoZ.getNum() + offlineDTO.getNum());
                                            dtoZ.setTotalPrice(dtoZ.getTotalPrice().add(offlineDTO.getTotalPrice()));
                                            hasAdd = true;
                                        }
                                    }
                                    if (!hasAdd){
                                        offlineMap.get(dto.getMediumOffline().getExcelShowTitleId()).getList().add(offlineDTO);
                                    }
                                }
                                if (!steadyContent.contains(offlineMap.get(dto.getMediumOffline().getExcelShowTitleId()).getSteadyContent())){
                                    offlineMap.get(dto.getMediumOffline().getExcelShowTitleId()).setSteadyContent(steadyContent + offlineMap.get(dto.getMediumOffline().getExcelShowTitleId()).getSteadyContent());
                                }
                            }else {
                                MediumOfflineAllExportDTO mediumOfflineAllExportDTO = new MediumOfflineAllExportDTO();
                                mediumOfflineAllExportDTO.setMediumOfflineType(dto.getOfflineMediumType());
                                mediumOfflineAllExportDTO.setExcelShowTitleId(dto.getMediumOffline().getExcelShowTitleId());
                                mediumOfflineAllExportDTO.setMediumOfflineTypeName(dto.getMediumOffline().getOfflineTypeName());
                                mediumOfflineAllExportDTO.setObjectClass(MediumOfflineExportDTOByGT.class);
                                mediumOfflineAllExportDTO.setSteadyContent(steadyContent);
                                mediumOfflineAllExportDTO.setExcelShowTitle(dto.getMediumOffline().getExcelShowTitle());
                                List<MediumOfflineExportDTOByGT> mediumOfflineExportDTOList = new ArrayList<>();
                                mediumOfflineExportDTOList.add(offlineDTO);
                                mediumOfflineAllExportDTO.setList(mediumOfflineExportDTOList);
                                offlineMap.put(dto.getMediumOffline().getExcelShowTitleId(), mediumOfflineAllExportDTO);
                            }
                        }else if (dto.getMediumOffline().getExcelShowTitleId() == 498){
                            String steadyContent = "";
                            MediumOfflineExportDTOByHWDP offlineDTO = new MediumOfflineExportDTOByHWDP();
                            offlineDTO.setId(dto.getLineId());
                            offlineDTO.setIndexNum(i + 1);
                            offlineDTO.setCity(dto.getMediumOffline().getCity());
                            offlineDTO.setPositionSpecs((StringUtil.isNotBlank(dto.getMediumOffline().getAddress()) ? dto.getMediumOffline().getAddress() : "") + " \n " + dto.getMediumOffline().getPositionSpecs());
                            offlineDTO.setPublicationSecondsAndBroadcastFrequency(dto.getMediumOffline().getPublicationSeconds() + "，" + dto.getMediumOffline().getBroadcastFrequency());
                            offlineDTO.setScreenNum(dto.getMediumOffline().getScreenNum());
                            offlineDTO.setGoodPrice(dto.getMediumOffline().getGoodPrice());
                            Integer num = dto.getNum();
                            if (dto.getMediumOffline().getPutInCycle().contains("月")){
                                num = num * 4;
                            }
                            offlineDTO.setNum(num);
                            offlineDTO.setTotalPrice(dto.getTotalPrice());
                            offlineDTO.setPic(dto.getMediumOffline().getPic());
                            if (StringUtil.isNotBlank(dto.getMediumOffline().getSteadyContent()) && !steadyContent.contains(dto.getMediumOffline().getSteadyContent())){
                                steadyContent = steadyContent + dto.getMediumOffline().getSteadyContent() + "\n";
                            }
                            if (offlineMap.containsKey(dto.getMediumOffline().getExcelShowTitleId())){
                                if (offlineMap.get(dto.getMediumOffline().getExcelShowTitleId()).getList() == null){
                                    List<MediumOfflineExportDTOByHWDP> mediumOfflineExportDTOList = new ArrayList<>();
                                    mediumOfflineExportDTOList.add(offlineDTO);
                                    offlineMap.get(dto.getMediumOffline().getExcelShowTitleId()).setList(mediumOfflineExportDTOList);
                                }else {
                                    boolean hasAdd = false;
                                    for (int z = 0;z<offlineMap.get(dto.getMediumOffline().getExcelShowTitleId()).getList().size();z++){
                                        MediumOfflineExportDTOByHWDP dtoZ = (MediumOfflineExportDTOByHWDP) offlineMap.get(dto.getMediumOffline().getExcelShowTitleId()).getList().get(z);
                                        if (dtoZ.getId() == offlineDTO.getId()){
                                            dtoZ.setNum(dtoZ.getNum() + offlineDTO.getNum());
                                            dtoZ.setTotalPrice(dtoZ.getTotalPrice().add(offlineDTO.getTotalPrice()));
                                            hasAdd = true;
                                        }
                                    }
                                    if (!hasAdd){
                                        offlineMap.get(dto.getMediumOffline().getExcelShowTitleId()).getList().add(offlineDTO);
                                    }
                                }
                                if (!steadyContent.contains(offlineMap.get(dto.getMediumOffline().getExcelShowTitleId()).getSteadyContent())){
                                    offlineMap.get(dto.getMediumOffline().getExcelShowTitleId()).setSteadyContent(steadyContent + offlineMap.get(dto.getMediumOffline().getExcelShowTitleId()).getSteadyContent());
                                }
                            }else {
                                MediumOfflineAllExportDTO mediumOfflineAllExportDTO = new MediumOfflineAllExportDTO();
                                mediumOfflineAllExportDTO.setMediumOfflineType(dto.getOfflineMediumType());
                                mediumOfflineAllExportDTO.setExcelShowTitleId(dto.getMediumOffline().getExcelShowTitleId());
                                mediumOfflineAllExportDTO.setMediumOfflineTypeName(dto.getMediumOffline().getOfflineTypeName());
                                mediumOfflineAllExportDTO.setObjectClass(MediumOfflineExportDTOByHWDP.class);
                                mediumOfflineAllExportDTO.setSteadyContent(steadyContent);
                                mediumOfflineAllExportDTO.setExcelShowTitle(dto.getMediumOffline().getExcelShowTitle());
                                List<MediumOfflineExportDTOByHWDP> mediumOfflineExportDTOList = new ArrayList<>();
                                mediumOfflineExportDTOList.add(offlineDTO);
                                mediumOfflineAllExportDTO.setList(mediumOfflineExportDTOList);
                                offlineMap.put(dto.getMediumOffline().getExcelShowTitleId(), mediumOfflineAllExportDTO);
                            }
                        }else if (dto.getMediumOffline().getExcelShowTitleId() == 495){
                            String steadyContent = "";
                            MediumOfflineExportDTOBySCCJP offlineDTO = new MediumOfflineExportDTOBySCCJP();
                            offlineDTO.setId(dto.getLineId());
                            offlineDTO.setIndexNum(i + 1);
                            offlineDTO.setName(dto.getMediumOffline().getName());
                            offlineDTO.setProvince(dto.getMediumOffline().getProvince());
                            offlineDTO.setScreenPosition(dto.getMediumOffline().getScreenPosition());
                            offlineDTO.setMarketNum(dto.getMediumOffline().getMarketNum());
                            offlineDTO.setShowTypeContent(dto.getMediumOffline().getShowTypeContent());
                            Integer num = dto.getNum();
                            if (dto.getMediumOffline().getPutInCycle().contains("月")){
                                num = num * 4;
                            }
                            offlineDTO.setNum(num);
                            offlineDTO.setTotalPrice(dto.getTotalPrice());
                            offlineDTO.setPic(dto.getMediumOffline().getPic());
                            if (StringUtil.isNotBlank(dto.getMediumOffline().getSteadyContent()) && !steadyContent.contains(dto.getMediumOffline().getSteadyContent())){
                                steadyContent = steadyContent + dto.getMediumOffline().getSteadyContent() + "\n";
                            }
                            if (offlineMap.containsKey(dto.getMediumOffline().getExcelShowTitleId())){
                                if (offlineMap.get(dto.getMediumOffline().getExcelShowTitleId()).getList() == null){
                                    List<MediumOfflineExportDTOBySCCJP> mediumOfflineExportDTOList = new ArrayList<>();
                                    mediumOfflineExportDTOList.add(offlineDTO);
                                    offlineMap.get(dto.getMediumOffline().getExcelShowTitleId()).setList(mediumOfflineExportDTOList);
                                }else {
                                    boolean hasAdd = false;
                                    for (int z = 0;z<offlineMap.get(dto.getMediumOffline().getExcelShowTitleId()).getList().size();z++){
                                        MediumOfflineExportDTOBySCCJP dtoZ = (MediumOfflineExportDTOBySCCJP) offlineMap.get(dto.getMediumOffline().getExcelShowTitleId()).getList().get(z);
                                        if (dtoZ.getId() == offlineDTO.getId()){
                                            dtoZ.setNum(dtoZ.getNum() + offlineDTO.getNum());
                                            dtoZ.setTotalPrice(dtoZ.getTotalPrice().add(offlineDTO.getTotalPrice()));
                                            hasAdd = true;
                                        }
                                    }
                                    if (!hasAdd){
                                        offlineMap.get(dto.getMediumOffline().getExcelShowTitleId()).getList().add(offlineDTO);
                                    }
                                }
                                if (!steadyContent.contains(offlineMap.get(dto.getMediumOffline().getExcelShowTitleId()).getSteadyContent())){
                                    offlineMap.get(dto.getMediumOffline().getExcelShowTitleId()).setSteadyContent(steadyContent + offlineMap.get(dto.getMediumOffline().getExcelShowTitleId()).getSteadyContent());
                                }
                            }else {
                                MediumOfflineAllExportDTO mediumOfflineAllExportDTO = new MediumOfflineAllExportDTO();
                                mediumOfflineAllExportDTO.setMediumOfflineType(dto.getOfflineMediumType());
                                mediumOfflineAllExportDTO.setExcelShowTitleId(dto.getMediumOffline().getExcelShowTitleId());
                                mediumOfflineAllExportDTO.setMediumOfflineTypeName(dto.getMediumOffline().getOfflineTypeName());
                                mediumOfflineAllExportDTO.setObjectClass(MediumOfflineExportDTOBySCCJP.class);
                                mediumOfflineAllExportDTO.setSteadyContent(steadyContent);
                                mediumOfflineAllExportDTO.setExcelShowTitle(dto.getMediumOffline().getExcelShowTitle());
                                List<MediumOfflineExportDTOBySCCJP> mediumOfflineExportDTOList = new ArrayList<>();
                                mediumOfflineExportDTOList.add(offlineDTO);
                                mediumOfflineAllExportDTO.setList(mediumOfflineExportDTOList);
                                offlineMap.put(dto.getMediumOffline().getExcelShowTitleId(), mediumOfflineAllExportDTO);
                            }
                        }else {
                            String steadyContent = "";
                            MediumOfflineExportDTO offlineDTO = new MediumOfflineExportDTO();
                            offlineDTO.setId(dto.getLineId());
                            offlineDTO.setIndexNum(i + 1);
                            offlineDTO.setCoverContent(dto.getMediumOffline().getCoverContent());
                            offlineDTO.setScreenNum(dto.getMediumOffline().getScreenNum());
                            offlineDTO.setPublicationSeconds(dto.getMediumOffline().getPublicationSeconds());
                            offlineDTO.setOneDayOperationTime(dto.getMediumOffline().getOneDayOperationTime());
                            offlineDTO.setSourceMaterialRequired(dto.getMediumOffline().getSourceMaterialRequired());
                            offlineDTO.setGoodPrice(dto.getMediumOffline().getGoodPrice());
                            Integer num = dto.getNum();
                            if (dto.getMediumOffline().getPutInCycle().contains("月")){
                                num = num * 4;
                            }
                            offlineDTO.setNum(num);
                            offlineDTO.setTotalPrice(dto.getTotalPrice());
                            offlineDTO.setPic(dto.getMediumOffline().getPic());
                            if (StringUtil.isNotBlank(dto.getMediumOffline().getSteadyContent()) && !steadyContent.contains(dto.getMediumOffline().getSteadyContent())){
                                steadyContent = steadyContent + dto.getMediumOffline().getSteadyContent() + "\n";
                            }
                            if (offlineMap.containsKey(dto.getMediumOffline().getExcelShowTitleId())){
                                if (offlineMap.get(dto.getMediumOffline().getExcelShowTitleId()).getList() == null){
                                    List<MediumOfflineExportDTO> mediumOfflineExportDTOList = new ArrayList<>();
                                    mediumOfflineExportDTOList.add(offlineDTO);
                                    offlineMap.get(dto.getMediumOffline().getExcelShowTitleId()).setList(mediumOfflineExportDTOList);
                                }else {
                                    boolean hasAdd = false;
                                    for (int z = 0;z<offlineMap.get(dto.getMediumOffline().getExcelShowTitleId()).getList().size();z++){
                                        MediumOfflineExportDTO dtoZ = (MediumOfflineExportDTO) offlineMap.get(dto.getMediumOffline().getExcelShowTitleId()).getList().get(z);
                                        if (dtoZ.getId() == offlineDTO.getId()){
                                            dtoZ.setNum(dtoZ.getNum() + offlineDTO.getNum());
                                            dtoZ.setTotalPrice(dtoZ.getTotalPrice().add(offlineDTO.getTotalPrice()));
                                            hasAdd = true;
                                        }
                                    }
                                    if (!hasAdd){
                                        offlineMap.get(dto.getMediumOffline().getExcelShowTitleId()).getList().add(offlineDTO);
                                    }
                                }
                                if (!steadyContent.contains(offlineMap.get(dto.getMediumOffline().getExcelShowTitleId()).getSteadyContent())){
                                    offlineMap.get(dto.getMediumOffline().getExcelShowTitleId()).setSteadyContent(steadyContent + offlineMap.get(dto.getMediumOffline().getExcelShowTitleId()).getSteadyContent());
                                }
                            }else {
                                MediumOfflineAllExportDTO mediumOfflineAllExportDTO = new MediumOfflineAllExportDTO();
                                mediumOfflineAllExportDTO.setMediumOfflineType(dto.getOfflineMediumType());
                                mediumOfflineAllExportDTO.setExcelShowTitleId(dto.getMediumOffline().getExcelShowTitleId());
                                mediumOfflineAllExportDTO.setMediumOfflineTypeName(dto.getMediumOffline().getOfflineTypeName());
                                mediumOfflineAllExportDTO.setObjectClass(MediumOfflineExportDTO.class);
                                mediumOfflineAllExportDTO.setSteadyContent(steadyContent);
                                mediumOfflineAllExportDTO.setExcelShowTitle(dto.getMediumOffline().getExcelShowTitle());
                                List<MediumOfflineExportDTO> mediumOfflineExportDTOList = new ArrayList<>();
                                mediumOfflineExportDTOList.add(offlineDTO);
                                mediumOfflineAllExportDTO.setList(mediumOfflineExportDTOList);
                                offlineMap.put(dto.getMediumOffline().getExcelShowTitleId(), mediumOfflineAllExportDTO);
                            }
                        }
                    }
                }
            }
            if (offlineMap.size() > 0){
                Set<Integer> integers = offlineMap.keySet();
                for (Integer titleId : integers){
                    SysLabelValue sysLabelValue = sysLabelValueMapper.selectByPrimaryKey(titleId);
                    offlineMap.get(titleId).setSteadyContent(sysLabelValue.getDesc());
                }
            }
            getExportListExcelOld(excelName, out, onlineExportList, offlineMap);
            out.close();
            System.out.println("excel导出成功！");
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public void getExportListExcelOld(String title, OutputStream out, List<MediumOnlineExportDTO> onlineExportList,
                                   Map<Integer, MediumOfflineAllExportDTO> offlineMap) throws Exception{
        BigDecimal projectTotalPrice = BigDecimal.ZERO;
        //声明一个工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
        //生成一个表格
        HSSFSheet sheet = workbook.createSheet(title);
        //设置表格默认列宽度为15个字符
        sheet.setDefaultColumnWidth(20);
        sheet.setDefaultRowHeight((short) 10);
        sheet.setDefaultRowHeightInPoints(10);
        sheet.setColumnWidth(0, 1500);
        sheet.setColumnWidth(1, 2000);
        //生成一个样式，用来设置标题样式
        HSSFCellStyle style = workbook.createCellStyle();
        //设置这些样式
        HSSFColor hssfColor = setColor(workbook, (byte) 145, (byte) 170, (byte) 223);
        style.setFillForegroundColor(hssfColor.getIndex());
        // 填充效果（全景填充）
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置边框
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        //生成一个字体
        HSSFFont font = workbook.createFont();
        font.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        font.setBold(true);
        font.setFontHeightInPoints((short) 11);
        font.setFontName("微软雅黑");
        //把字体应用到当前的样式
        style.setFont(font);
        // 生成并设置另一个样式,用于设置内容样式
        HSSFCellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
        // 填充效果（全景填充）
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置边框
        headerStyle.setBorderBottom(BorderStyle.THIN);
        headerStyle.setBorderLeft(BorderStyle.THIN);
        headerStyle.setBorderRight(BorderStyle.THIN);
        headerStyle.setBorderTop(BorderStyle.THIN);
        // 生成另一个字体
        HSSFFont font2 = workbook.createFont();
        font2.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        font2.setBold(true);
        font2.setFontHeightInPoints((short) 11);
        font2.setFontName("微软雅黑");
        headerStyle.setFont(font2);

        // 生成价格类型的样式
        HSSFCellStyle priceStyle = workbook.createCellStyle();
        priceStyle.setAlignment(HorizontalAlignment.CENTER);
        priceStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        priceStyle.setBorderBottom(BorderStyle.THIN);
        priceStyle.setBorderLeft(BorderStyle.THIN);
        priceStyle.setBorderRight(BorderStyle.THIN);
        priceStyle.setBorderTop(BorderStyle.THIN);
        HSSFFont priceFont = workbook.createFont();
        priceFont.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        priceFont.setBold(true);
        priceFont.setFontHeightInPoints((short) 11);
        priceFont.setFontName("微软雅黑");
        priceStyle.setFont(priceFont);

        // 生成数量类型的样式
        HSSFCellStyle numberStyle = workbook.createCellStyle();
        numberStyle.setAlignment(HorizontalAlignment.CENTER);
        numberStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        numberStyle.setBorderBottom(BorderStyle.THIN);
        numberStyle.setBorderLeft(BorderStyle.THIN);
        numberStyle.setBorderRight(BorderStyle.THIN);
        numberStyle.setBorderTop(BorderStyle.THIN);
        numberStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
        HSSFFont numberFont = workbook.createFont();
        numberFont.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        numberFont.setBold(true);
        numberFont.setFontHeightInPoints((short) 11);
        numberFont.setFontName("微软雅黑");
        numberStyle.setFont(numberFont);

        //生成一个样式，用来设置内容样式
        HSSFCellStyle textStyle = workbook.createCellStyle();
        textStyle.setAlignment(HorizontalAlignment.CENTER);
        textStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        textStyle.setBorderBottom(BorderStyle.THIN);
        textStyle.setBorderLeft(BorderStyle.THIN);
        textStyle.setBorderRight(BorderStyle.THIN);
        textStyle.setBorderTop(BorderStyle.THIN);
        textStyle.setWrapText(true); // 自动换行
        HSSFFont textFont = workbook.createFont();
        textFont.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        textFont.setFontHeightInPoints((short) 11);
        textFont.setFontName("微软雅黑");
        textStyle.setFont(textFont);


        // 加粗样式
        HSSFCellStyle textBoldStyle = workbook.createCellStyle();
        textBoldStyle.setAlignment(HorizontalAlignment.CENTER);
        textBoldStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        textBoldStyle.setBorderBottom(BorderStyle.THIN);
        textBoldStyle.setBorderLeft(BorderStyle.THIN);
        textBoldStyle.setBorderRight(BorderStyle.THIN);
        textBoldStyle.setBorderTop(BorderStyle.THIN);
        textBoldStyle.setWrapText(true); // 自动换行
        HSSFFont textBoldFont = workbook.createFont();
        textBoldFont.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        textBoldFont.setFontHeightInPoints((short) 11);
        textBoldFont.setFontName("微软雅黑");
        textBoldFont.setBold(true);
        textBoldStyle.setFont(textBoldFont);

        //生成一个小计样式
        HSSFCellStyle totalTitleStyle = workbook.createCellStyle();
        totalTitleStyle.setAlignment(HorizontalAlignment.RIGHT);
        totalTitleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        totalTitleStyle.setBorderBottom(BorderStyle.THIN);
        totalTitleStyle.setBorderLeft(BorderStyle.THIN);
        totalTitleStyle.setBorderRight(BorderStyle.THIN);
        totalTitleStyle.setBorderTop(BorderStyle.THIN);
        HSSFFont totalFont = workbook.createFont();
        totalFont.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        totalFont.setBold(true);
        totalFont.setFontHeightInPoints((short) 11);
        totalFont.setFontName("微软雅黑");
        totalTitleStyle.setFont(totalFont);
        HSSFCellStyle totalContentStyle = workbook.createCellStyle();
        totalContentStyle.setAlignment(HorizontalAlignment.CENTER);
        totalContentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        totalContentStyle.setBorderBottom(BorderStyle.THIN);
        totalContentStyle.setBorderLeft(BorderStyle.THIN);
        totalContentStyle.setBorderRight(BorderStyle.THIN);
        totalContentStyle.setBorderTop(BorderStyle.THIN);
        totalContentStyle.setFont(totalFont);

        //生成一个最终合计样式
        HSSFCellStyle projectTotalTitleStyle = workbook.createCellStyle();
        projectTotalTitleStyle.setAlignment(HorizontalAlignment.RIGHT);
        projectTotalTitleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        projectTotalTitleStyle.setBorderBottom(BorderStyle.THIN);
        projectTotalTitleStyle.setBorderLeft(BorderStyle.THIN);
        projectTotalTitleStyle.setBorderRight(BorderStyle.THIN);
        projectTotalTitleStyle.setBorderTop(BorderStyle.THIN);
        HSSFFont projectTotalFont = workbook.createFont();
        projectTotalFont.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        projectTotalFont.setBold(true);
        projectTotalFont.setFontHeightInPoints((short) 11);
        projectTotalFont.setFontName("微软雅黑");
        projectTotalTitleStyle.setFont(projectTotalFont);
        HSSFCellStyle projectTotalContentStyle = workbook.createCellStyle();
        projectTotalContentStyle.setAlignment(HorizontalAlignment.CENTER);
        projectTotalContentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        projectTotalContentStyle.setBorderBottom(BorderStyle.THIN);
        projectTotalContentStyle.setBorderLeft(BorderStyle.THIN);
        projectTotalContentStyle.setBorderRight(BorderStyle.THIN);
        projectTotalContentStyle.setBorderTop(BorderStyle.THIN);
        projectTotalContentStyle.setFont(totalFont);


        //生成一个样式，用来设置注意样式
        HSSFCellStyle steadyContentStyle = workbook.createCellStyle();
        steadyContentStyle.setAlignment(HorizontalAlignment.LEFT);
        steadyContentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        steadyContentStyle.setBorderBottom(BorderStyle.THIN);
        steadyContentStyle.setBorderLeft(BorderStyle.THIN);
        steadyContentStyle.setBorderRight(BorderStyle.THIN);
        steadyContentStyle.setBorderTop(BorderStyle.THIN);
        steadyContentStyle.setWrapText(true); // 自动换行
        HSSFFont steadyContentFont = workbook.createFont();
        steadyContentFont.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        steadyContentFont.setBold(true);
        steadyContentFont.setFontHeightInPoints((short) 11);
        steadyContentFont.setFontName("微软雅黑");
        steadyContentStyle.setFont(steadyContentFont);

        // 固定标题样式
        HSSFCellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        titleStyle.setBorderBottom(BorderStyle.THIN);
        titleStyle.setBorderLeft(BorderStyle.THIN);
        titleStyle.setBorderRight(BorderStyle.THIN);
        titleStyle.setBorderTop(BorderStyle.THIN);
        HSSFFont titleFont = workbook.createFont();
        titleFont.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        titleFont.setBold(true);
        titleFont.setFontHeightInPoints((short) 11);
        titleFont.setFontName("微软雅黑");
        titleStyle.setFont(titleFont);

        Integer rowNum = 1;
        Integer addIndexNum = 1;
        Integer headerSize = 0;

//        rowNum = rowNum + 1;
//        // 生成标题-资源配包
//        HSSFRow titleCountRow = sheet.createRow(rowNum);
//        titleCountRow.setHeightInPoints(20);
//        CellRangeAddress titleCountRegion = new CellRangeAddress(rowNum, rowNum, 1, 8);
//        sheet.addMergedRegion(titleCountRegion);
//        //添加边框
//        RegionUtil.setBorderTop(BorderStyle.THIN, titleCountRegion, sheet);
//        RegionUtil.setBorderBottom(BorderStyle.THIN, titleCountRegion, sheet);
//        RegionUtil.setBorderLeft(BorderStyle.THIN, titleCountRegion, sheet);
//        RegionUtil.setBorderRight(BorderStyle.THIN, titleCountRegion, sheet);
//        HSSFCell titleCountCell = titleCountRow.createCell(1);
//        titleCountCell.setCellStyle(titleStyle);
//        HSSFRichTextString titleCountText = new HSSFRichTextString("资源配包");
//        titleCountCell.setCellValue(titleCountText);


        rowNum = rowNum + 1;
        HSSFRow titleContentRow = sheet.createRow(rowNum);
        titleContentRow.setHeightInPoints(30);
        CellRangeAddress titleContentRegion = new CellRangeAddress(rowNum, rowNum, 1, 8);
        sheet.addMergedRegion(titleContentRegion);
        RegionUtil.setBorderTop(BorderStyle.THIN, titleContentRegion, sheet);
        RegionUtil.setBorderBottom(BorderStyle.THIN, titleContentRegion, sheet);
        RegionUtil.setBorderLeft(BorderStyle.THIN, titleContentRegion, sheet);
        RegionUtil.setBorderRight(BorderStyle.THIN, titleContentRegion, sheet);
        HSSFCell titleCountCell = titleContentRow.createCell(1);
        titleCountCell.setCellStyle(titleStyle);
        HSSFRichTextString titleCountText = new HSSFRichTextString("资源配包");
        titleCountCell.setCellValue(titleCountText);
        rowNum = rowNum + 1;

        if (!BeanUtil.isNullList(onlineExportList)){

            // 标题
            HSSFRow titleRow = sheet.createRow(rowNum);
            HSSFCell titleCell = titleRow.createCell(1);
            titleRow.setHeightInPoints(20);
            titleCell.setCellStyle(headerStyle);
            HSSFRichTextString titleText = new HSSFRichTextString(addIndexNum + ".线上达人");
            titleCell.setCellValue(titleText);
            addIndexNum = addIndexNum + 1;
            rowNum = rowNum + 1;

            // 获取excel显示第一行的字段名
            List<String> headers = Lists.newArrayList();
            // 生成header
            MediumOnlineExportDTO msg = new MediumOnlineExportDTO();
            Field[] declaredFieldsHeader = msg.getClass().getDeclaredFields();
            for (Field field : declaredFieldsHeader){
                field.setAccessible(true);
                boolean hidden = field.getAnnotation(ApiModelProperty.class).hidden();
                if (!hidden){
                    String value = field.getAnnotation(ApiModelProperty.class).value();
                    headers.add(value);
                }
            }
            headerSize = headers.size();

            //产生表格标题行
            HSSFRow row = sheet.createRow(rowNum);
            row.setHeightInPoints(20);
            // 合并单元格
            CellRangeAddress region = new CellRangeAddress(rowNum - 1, rowNum - 1, 1, headers.size());
            sheet.addMergedRegion(region);
            //添加边框
            RegionUtil.setBorderTop(BorderStyle.THIN, region, sheet);
            RegionUtil.setBorderBottom(BorderStyle.THIN, region, sheet);
            RegionUtil.setBorderLeft(BorderStyle.THIN, region, sheet);
            RegionUtil.setBorderRight(BorderStyle.THIN, region, sheet);
            for (int i = 0; i < headers.size(); i++) {
                HSSFCell cell = row.createCell(i + 1);
                cell.setCellStyle(style);
                HSSFRichTextString text = new HSSFRichTextString(headers.get(i));
                cell.setCellValue(text);
            }

            Integer totalNum = 0;
            BigDecimal totalPrice = BigDecimal.ZERO;
            for (int i = 0; i < onlineExportList.size(); i++) {
                rowNum = rowNum + 1;
                row = sheet.createRow(rowNum);
                int j = 1;
                MediumOnlineExportDTO dto = (MediumOnlineExportDTO) onlineExportList.get(i);
                totalNum = totalNum + dto.getNum();
                totalPrice = totalPrice.add(dto.getTotalPrice());
                dto.setIndexNum(i + 1);
                // 获取excel里对应的值
                Field[] declaredFields = dto.getClass().getDeclaredFields();
//                for (Field field : declaredFields){
                for (int a = 0; a < declaredFields.length; a++) {
                    Field field = declaredFields[a];
                    field.setAccessible(true);
                    Object o = field.get(dto);
                    HSSFCell cell = row.createCell(j++);
                    row.setHeightInPoints(20);
                    cell.setCellStyle(textStyle);

                    if (a == 1) {
                        cell.setCellStyle(textBoldStyle);
                    }
                    if (o == null) {
                        cell.setCellValue("");
                    } else {
                        if (o instanceof BigDecimal) {
                            DataFormat dataFormat = workbook.createDataFormat();  //创建一个单元格数据格式
                            priceStyle.setDataFormat(dataFormat.getFormat("#,##0.00"));  //设置数据格式为数字
                            cell.setCellStyle(priceStyle);
                            if (JSON.toJSONString(o).equals("0E-8")) {
                                cell.setCellValue(0);
                            } else if (JSON.toJSONString(o).equals("0E-24")) {
                                cell.setCellValue(0);
                            } else {
                                cell.setCellValue(((BigDecimal) o).longValue());
                            }
                        } else if (o instanceof Number) {
                            DataFormat dataFormat = workbook.createDataFormat();  //创建一个单元格数据格式
                            numberStyle.setDataFormat(dataFormat.getFormat("#,##0.00"));  //设置数据格式为数字
                            cell.setCellStyle(numberStyle);
                            if (JSON.toJSONString(o).equals("0E-8")) {
                                cell.setCellValue(0);
                            } else if (JSON.toJSONString(o).equals("0E-24")) {
                                cell.setCellValue(0);
                            } else {
                                cell.setCellValue(((Number) o).longValue());
                            }
                        } else {
                            cell.setCellValue(o.toString());
                        }
                    }
                }
            }
            // 合计
            rowNum = rowNum + 1;
            HSSFRow totalCountRow = sheet.createRow(rowNum);
            totalCountRow.setHeightInPoints(20);
            CellRangeAddress totalCountRegion = new CellRangeAddress(rowNum, rowNum, 1, headers.size() - 2);
            sheet.addMergedRegion(totalCountRegion);
            //添加边框
            RegionUtil.setBorderTop(BorderStyle.THIN, totalCountRegion, sheet);
            RegionUtil.setBorderBottom(BorderStyle.THIN, totalCountRegion, sheet);
            RegionUtil.setBorderLeft(BorderStyle.THIN, totalCountRegion, sheet);
            RegionUtil.setBorderRight(BorderStyle.THIN, totalCountRegion, sheet);
            HSSFCell totalCountCell = totalCountRow.createCell(1);
            totalCountCell.setCellStyle(totalTitleStyle);
            HSSFRichTextString totalCountText = new HSSFRichTextString("合计：");
            totalCountCell.setCellValue(totalCountText);
            // 数量合计
            HSSFCell totalCountNumCell = totalCountRow.createCell(headers.size() - 1);
            totalCountNumCell.setCellStyle(totalContentStyle);
            if (JSON.toJSONString(totalNum).equals("0E-8")){
                totalCountNumCell.setCellValue(0);
            }else if (JSON.toJSONString(totalNum).equals("0E-24")){
                totalCountNumCell.setCellValue(0);
            }else {
                totalCountNumCell.setCellValue(((Number) totalNum).longValue());
            }
//            HSSFRichTextString totalCountNumText = new HSSFRichTextString(totalNum.toString());
//            totalCountNumCell.setCellValue(totalCountNumText);
            // 价格合计
            HSSFCell totalCountPriceCell = totalCountRow.createCell(headers.size());
            totalCountPriceCell.setCellStyle(totalContentStyle);
            if (JSON.toJSONString(totalPrice).equals("0E-8")){
                totalCountPriceCell.setCellValue(0);
            }else if (JSON.toJSONString(totalPrice).equals("0E-24")){
                totalCountPriceCell.setCellValue(0);
            }else {
                totalCountPriceCell.setCellValue(((Number) totalPrice).longValue());
            }
            projectTotalPrice = projectTotalPrice.add(totalPrice);
//            HSSFRichTextString totalCountPriceText = new HSSFRichTextString(totalPrice.toString());
//            totalCountPriceCell.setCellValue(totalCountPriceText);

        } else {
            rowNum = rowNum - 1;
        }
        if (offlineMap != null){
            for (MediumOfflineAllExportDTO offlineAllExportDTO : offlineMap.values()){

                // 标题
                rowNum = rowNum + 1;
                HSSFRow titleRow = sheet.createRow(rowNum);
                titleRow.setHeightInPoints(20);
                HSSFCell titleCell = titleRow.createCell(1);
                titleCell.setCellStyle(headerStyle);
                HSSFRichTextString titleText = new HSSFRichTextString(addIndexNum + "." + offlineAllExportDTO.getExcelShowTitle());
                titleCell.setCellValue(titleText);
                addIndexNum = addIndexNum + 1;

                // 获取excel显示第一行的字段名
                List<String> headers = Lists.newArrayList();

                String steadyContent = offlineAllExportDTO.getSteadyContent(); // 注意点
                if (!BeanUtil.isNullList(offlineAllExportDTO.getList())){
                    BigDecimal totalPrice = BigDecimal.ZERO;
                    // 生成header
                    Field[] declaredFieldsHeader = offlineAllExportDTO.getObjectClass().getDeclaredFields();
                    for (Field field : declaredFieldsHeader){
                        field.setAccessible(true);
                        boolean hidden = field.getAnnotation(ApiModelProperty.class).hidden();
                        if (!hidden){
                            String value = field.getAnnotation(ApiModelProperty.class).value();
                            boolean required = field.getAnnotation(ApiModelProperty.class).required();
                            if (StringUtil.isNotBlank(value) && !value.equals("steadyContent") && !required){
                                headers.add(value);
                            }
                        }
                    }
                    headerSize = headers.size();
                    // 合并单元格
                    CellRangeAddress region = new CellRangeAddress(rowNum, rowNum, 1, headers.size() - 1);
                    sheet.addMergedRegion(region);
                    //添加边框
                    RegionUtil.setBorderTop(BorderStyle.THIN, region, sheet);
                    RegionUtil.setBorderBottom(BorderStyle.THIN, region, sheet);
                    RegionUtil.setBorderLeft(BorderStyle.THIN, region, sheet);
                    RegionUtil.setBorderRight(BorderStyle.THIN, region, sheet);

                    //产生表格标题行
                    rowNum = rowNum + 1;
                    HSSFRow row = sheet.createRow(rowNum);
                    row.setHeightInPoints(20);
                    for (int i = 0; i < headers.size() - 1; i++) {
                        HSSFCell cell = row.createCell(i + 1);
                        cell.setCellStyle(style);
                        HSSFRichTextString text = new HSSFRichTextString(headers.get(i));
                        cell.setCellValue(text);
                    }

                    for (int i = 0; i < offlineAllExportDTO.getList().size(); i++){
                        rowNum = rowNum + 1;
                        row = sheet.createRow(rowNum);
                        int j = 1;
                        Object dto = offlineAllExportDTO.getList().get(i);
                        // 获取excel里对应的值
                        Field[] declaredFields = dto.getClass().getDeclaredFields();
                        for (int a = 0; a < declaredFields.length; a++) {
                            Field field = declaredFields[a];
                            field.setAccessible(true);
                            boolean hidden = field.getAnnotation(ApiModelProperty.class).hidden();
                            if (!hidden){
                                Object o = field.get(dto);
                                HSSFCell cell = row.createCell(j++);
                                row.setHeightInPoints(20);
                                cell.setCellStyle(textStyle);
                                if (a == 1){
                                    cell.setCellStyle(textBoldStyle);
                                }
                                if (o == null){
                                    cell.setCellValue("");
                                }else {
                                    if (o instanceof BigDecimal){
                                        if (field.getName().equals("totalPrice")){
                                            totalPrice = totalPrice.add(new BigDecimal(o.toString()));
                                        }
                                        DataFormat dataFormat = workbook.createDataFormat();  //创建一个单元格数据格式
                                        priceStyle.setDataFormat(dataFormat.getFormat("#,##0.00"));  //设置数据格式为数字
                                        cell.setCellStyle(priceStyle);
                                        if (JSON.toJSONString(o).equals("0E-8")){
                                            cell.setCellValue(0);
                                        }else if (JSON.toJSONString(o).equals("0E-24")){
                                            cell.setCellValue(0);
                                        }else {
                                            cell.setCellValue(((BigDecimal) o).longValue());
                                        }
                                    }else if (o instanceof Number){
                                        DataFormat dataFormat = workbook.createDataFormat();  //创建一个单元格数据格式
                                        numberStyle.setDataFormat(dataFormat.getFormat("#,##0.00"));  //设置数据格式为数字
                                        cell.setCellStyle(numberStyle);
                                        if (JSON.toJSONString(o).equals("0E-8")){
                                            cell.setCellValue(0);
                                        }else if (JSON.toJSONString(o).equals("0E-24")){
                                            cell.setCellValue(0);
                                        }else {
                                            cell.setCellValue(((Number) o).longValue());
                                        }
                                    }else {
                                        String v = o.toString();
                                        if (o.toString().indexOf("\n") != -1){
                                            v = v.replace("\n", "");
                                        }
                                        cell.setCellValue(v);
                                    }
                                    if (field.getName().equals("indexNum")){
                                        cell.setCellValue(i + 1);
                                    }
                                    if (field.getName().equals("pic")){
                                        cell.setCellValue("");
                                        if (StringUtil.isNotBlank(o.toString())){
                                            int size = headers.size();
                                            String reference = field.getAnnotation(ApiModelProperty.class).reference();
                                            if (reference != null && reference.equals("show2")){
                                                size = size + 1;
                                            }
                                            drawPictureInfoExcel(workbook, row, o.toString(), sheet, rowNum, size);
                                        }else {
                                            cell.setCellStyle(null);
                                        }
                                    }
                                }
                            }
                        }
                    }
                    // 合计
                    rowNum = rowNum + 1;
                    HSSFRow totalCountRow = sheet.createRow(rowNum);
                    totalCountRow.setHeightInPoints(20);
                    CellRangeAddress totalCountRegion = new CellRangeAddress(rowNum, rowNum, 1, headers.size() - 2);
                    sheet.addMergedRegion(totalCountRegion);
                    //添加边框
                    RegionUtil.setBorderTop(BorderStyle.THIN, totalCountRegion, sheet);
                    RegionUtil.setBorderBottom(BorderStyle.THIN, totalCountRegion, sheet);
                    RegionUtil.setBorderLeft(BorderStyle.THIN, totalCountRegion, sheet);
                    RegionUtil.setBorderRight(BorderStyle.THIN, totalCountRegion, sheet);

                    HSSFCell totalCountCell = totalCountRow.createCell(1);
                    totalCountCell.setCellStyle(totalTitleStyle);
                    HSSFRichTextString totalCountText = new HSSFRichTextString("小计：");
                    totalCountCell.setCellValue(totalCountText);
                    // 价格合计
                    HSSFCell totalCountPriceCell = totalCountRow.createCell(headers.size() - 1);
                    totalCountPriceCell.setCellStyle(totalContentStyle);
                    if (JSON.toJSONString(totalPrice).equals("0E-8")){
                        totalCountPriceCell.setCellValue(0);
                    }else if (JSON.toJSONString(totalPrice).equals("0E-24")){
                        totalCountPriceCell.setCellValue(0);
                    }else {
                        totalCountPriceCell.setCellValue(((Number) totalPrice).longValue());
                    }
                    projectTotalPrice = projectTotalPrice.add(totalPrice);
//                    HSSFRichTextString totalCountPriceText = new HSSFRichTextString(totalPrice.toString());
//                    totalCountPriceCell.setCellValue(totalCountPriceText);


                    // 注意点
                    if (StringUtil.isNotBlank(steadyContent)){
                        rowNum = rowNum + 1;
                        HSSFRow steadyContentRow = sheet.createRow(rowNum);
                        steadyContentRow.setHeightInPoints(100);
                        CellRangeAddress steadyContentRegion = new CellRangeAddress(rowNum, rowNum, 1, headers.size() - 1);
                        sheet.addMergedRegion(steadyContentRegion);
                        RegionUtil.setBorderTop(BorderStyle.THIN, steadyContentRegion, sheet);
                        RegionUtil.setBorderBottom(BorderStyle.THIN, steadyContentRegion, sheet);
                        RegionUtil.setBorderLeft(BorderStyle.THIN, steadyContentRegion, sheet);
                        RegionUtil.setBorderRight(BorderStyle.THIN, steadyContentRegion, sheet);
                        HSSFCell steadyContentCell = steadyContentRow.createCell(1);
                        steadyContentCell.setCellStyle(steadyContentStyle);
                        HSSFRichTextString steadyContentText = new HSSFRichTextString(steadyContent);
                        steadyContentCell.setCellValue(steadyContentText);
                    }

                }
            }
        }

        // 货值总计
        rowNum = rowNum + 1;
        HSSFRow projectTotalCountRow = sheet.createRow(rowNum);
        projectTotalCountRow.setHeightInPoints(50);
        CellRangeAddress totalCountRegion = new CellRangeAddress(rowNum, rowNum, 1, headerSize - 2);
        sheet.addMergedRegion(totalCountRegion);
        //添加边框
        RegionUtil.setBorderTop(BorderStyle.THIN, totalCountRegion, sheet);
        RegionUtil.setBorderBottom(BorderStyle.THIN, totalCountRegion, sheet);
        RegionUtil.setBorderLeft(BorderStyle.THIN, totalCountRegion, sheet);
        RegionUtil.setBorderRight(BorderStyle.THIN, totalCountRegion, sheet);
        HSSFCell projectTotalCountCell = projectTotalCountRow.createCell(1);
        projectTotalTitleStyle.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
        projectTotalTitleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        projectTotalCountCell.setCellStyle(projectTotalTitleStyle);
        HSSFRichTextString projectTotalCountText = new HSSFRichTextString("货值总计：");
        projectTotalCountCell.setCellValue(projectTotalCountText);
//        // 价格合计
        HSSFCell projectTotalCountPriceCell = projectTotalCountRow.createCell(headerSize - 1);
        projectTotalContentStyle.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
        projectTotalContentStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        projectTotalCountPriceCell.setCellStyle(projectTotalContentStyle);
        if (JSON.toJSONString(projectTotalPrice).equals("0E-8")){
            projectTotalCountPriceCell.setCellValue(0);
        }else if (JSON.toJSONString(projectTotalPrice).equals("0E-24")){
            projectTotalCountPriceCell.setCellValue(0);
        }else {
            projectTotalCountPriceCell.setCellValue(((Number) projectTotalPrice).longValue());
        }

        try {
            workbook.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 在指定的Excel表格中绘制图片信息
     *
     * @param imgRow 图片所在行
     * @param pictureUrl 图片文件路径
     * @param sheet 绘制图片的表格
     * @param i 图片所在列的序号
     * @param workBook 工作簿对象
     */
    @Async
    public void drawPictureInfoExcel(HSSFWorkbook workBook, HSSFRow imgRow, String pictureUrl, HSSFSheet sheet, int i, Integer workBookSize) {
        try {
            BufferedImage bufferedImage = null;
            HSSFPatriarch hSSFPatriarch = sheet.createDrawingPatriarch();// 画图管理器
            // 创建HttpClient
            CloseableHttpClient httpClient = HttpClients.createDefault();
            // 创建HttpGet请求
            HttpGet httpGet = new HttpGet(pictureUrl);
            // 执行请求
            CloseableHttpResponse response = httpClient.execute(httpGet);
            // 获得响应实体
            InputStream inputStream = response.getEntity().getContent();
            // 创建临时文件
            File file = File.createTempFile("temp", ".png");
            // 写入文件
            FileUtils.copyInputStreamToFile(inputStream, file);

//            File file = new File(pictureUrl);
            imgRow.setHeight((short) 1000);// 设置高度
            Image src = Toolkit.getDefaultToolkit().getImage(file.getPath());
            bufferedImage = toBufferedImage(src);//Image to BufferedImage
//            bufferedImage = ImageIO.read(file);
            ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
            ImageIO.write(bufferedImage, "jpg", byteArrayOutputStream);
            HSSFClientAnchor hSSFClientAnchor = new HSSFClientAnchor(
                    20, /* 开始点 x坐标,范围 0-1023 */
                    10, /* 开始点y坐标范.围 0-255 */
                    1003, /* 结束点x坐标,范围 0-1023 */
                    245, /* 结束点y坐标,范围 0-255 */
                    Short.valueOf(String.valueOf(workBookSize)), /* 开始单元格列号,范围0-255 */
                    i, /* 开始单元格行号,范围0-255 * 256 */
                    Short.valueOf(String.valueOf(workBookSize)), /* 结束单元格列号,范围0-255 */
                    i/* 结束单元格行号,范围0-255 * 256 */
            );
            hSSFPatriarch.createPicture(hSSFClientAnchor,
                    workBook.addPicture(byteArrayOutputStream.toByteArray(), XSSFWorkbook.PICTURE_TYPE_JPEG));

            // 关闭流
            inputStream.close();
            // 关闭响应和HttpClient
            response.close();
            httpClient.close();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static BufferedImage toBufferedImage(Image image) {
        if (image instanceof BufferedImage) {
            return (BufferedImage) image;
        }
// This code ensures that all the pixels in the image are loaded
        image = new ImageIcon(image).getImage();
        BufferedImage bimage = null;
        GraphicsEnvironment ge = GraphicsEnvironment
                .getLocalGraphicsEnvironment();
        try {
            int transparency = Transparency.OPAQUE;
            GraphicsDevice gs = ge.getDefaultScreenDevice();
            GraphicsConfiguration gc = gs.getDefaultConfiguration();
            bimage = gc.createCompatibleImage(image.getWidth(null),
                    image.getHeight(null), transparency);
        } catch (HeadlessException e) {
// The system does not have a screen
        }
        if (bimage == null) {
// Create a buffered image using the default color model
            int type = BufferedImage.TYPE_INT_RGB;
            bimage = new BufferedImage(image.getWidth(null),
                    image.getHeight(null), type);
        }
// Copy image to buffered image
        Graphics g = bimage.createGraphics();
// Paint the image onto the buffered image
        g.drawImage(image, 0, 0, null);
        g.dispose();
        return bimage;
    }

    /**
     * 设置颜色
     *
     * @param workbook 工作簿对象
     * @param r 红色分量值
     * @param g 绿色分量值
     * @param b 蓝色分量值
     * @return HSSFColor对象
     */
    public HSSFColor setColor(HSSFWorkbook workbook, byte r,byte g, byte b){
        HSSFPalette palette = workbook.getCustomPalette();
        HSSFColor hssfColor = null;
        try {
            hssfColor= palette.findColor(r, g, b);
            if (hssfColor == null ){
                palette.setColorAtIndex(HSSFColor.HSSFColorPredefined.LAVENDER.getIndex(), r, g,b);
                hssfColor = palette.getColor(HSSFColor.HSSFColorPredefined.LAVENDER.getIndex());
            }
        } catch (Exception e) {
            logger.error(e.getMessage());
        }
        return hssfColor;
    }




    @ApiOperation(value = "导出配包")
    @ApiImplicitParams({
    })
    @ApiResponses({
            @ApiResponse(code = 2000, message = "出现异常，及时通知后台")
    })
    @ResponseBody
    @PostMapping(value = "/doExportPackage")
    public void doExportFile2(HttpServletResponse response,
                             @RequestParam int projectId,
                             @RequestParam int indexNum) throws Exception{
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/octet-stream;charset=UTF-8");
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMM");
        MjProject mjProject = projectMapper.selectByPrimaryKey(projectId);
        String excelName = mjProject.getBrands() + dateFormat.format(new Date()) + "配包方案";
        response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(excelName, "UTF-8") + ".xls");
        response.addHeader("filename", URLEncoder.encode(excelName, "UTF-8"));

        try {
            OutputStream out = response.getOutputStream();
            APPResultMessage successMessage = projectManageService.viewPackageManageProjectExportMsg(projectId,indexNum);

            List<ProjectMediumListDTO> list = successMessage.getResult().getList();
            List<MediumOnlineExportDTO> onlineExportList = new ArrayList<>();
            Map<Integer, OfflineDTO> offlineMap = new HashMap<>();
            if (!BeanUtil.isNullList(list)) {
                for (int i = 0; i < list.size(); i++){
                    ProjectMediumListDTO dto = list.get(i);
                    if (dto.getLineType() == 1){
                        MediumOnlineExportDTO mediumOnlineExportDTO = new MediumOnlineExportDTO();
                        mediumOnlineExportDTO.setIndexNum(onlineExportList.size() + 1);
                        mediumOnlineExportDTO.setPlateName(dto.getMediumOnline().getPlateName());
                        mediumOnlineExportDTO.setUserTypeAndCreateType(dto.getMediumOnline().getUserTypeName() + " - " + dto.getMediumOnline().getCreateType());
                        mediumOnlineExportDTO.setFans(dto.getMediumOnline().getFans());
                        mediumOnlineExportDTO.setGoodPrice(dto.getMediumOnline().getGoodPrice());
                        mediumOnlineExportDTO.setRemark(dto.getMediumOnline().getRemark());
                        mediumOnlineExportDTO.setNum(dto.getNum());
                        mediumOnlineExportDTO.setTotalPrice(dto.getTotalPrice());
                        mediumOnlineExportDTO.setOneExposureNum(dto.getMediumOnline().getOneExposureNum());
                        mediumOnlineExportDTO.setOneInteractionNum(dto.getMediumOnline().getOneInteractionNum());
                        onlineExportList.add(mediumOnlineExportDTO);
                    } else if (dto.getLineType() == 2){
                        BigDecimal oneWeekPrice = BigDecimal.ZERO;
                        if (dto.getNum() != null && dto.getNum() > 0 && dto.getMediumOffline().getGoodPrice() != null){
                            oneWeekPrice = dto.getMediumOffline().getGoodPrice().multiply(new BigDecimal(dto.getNum()));
                        }
                        OfflineDTO offlineDTO = getOfflineDTO(dto, oneWeekPrice);
                        if (offlineMap.containsKey(dto.getMediumOffline().getExcelShowTitleId())){
                            OfflineDTO offlineDTO1 = offlineMap.get(dto.getMediumOffline().getExcelShowTitleId());
                            offlineDTO1.getList().addAll(offlineDTO.getList());
                            offlineMap.put(dto.getMediumOffline().getExcelShowTitleId(), offlineDTO1);
                        }else {
                            offlineDTO.setExcelShowTitleId(dto.getMediumOffline().getExcelShowTitleId());
                            offlineDTO.setExcelShowTitle(dto.getMediumOffline().getExcelShowTitle());
                            offlineDTO.setSteadyContent(sysLabelValueMapper.selectByPrimaryKey(dto.getMediumOffline().getExcelShowTitleId()).getDesc());
                            offlineMap.put(dto.getMediumOffline().getExcelShowTitleId(), offlineDTO);
                        }
                    }
                }
            }
            if (offlineMap.size() > 0){
                Set<Integer> integers = offlineMap.keySet();
                for (Integer titleId : integers){
                    SysLabelValue sysLabelValue = sysLabelValueMapper.selectByPrimaryKey(titleId);
                    offlineMap.get(titleId).setSteadyContent(sysLabelValue.getDesc());
                }
            }
            getExportListExcel(excelName, out, onlineExportList, offlineMap);
            out.close();
            System.out.println("excel2导出成功！");
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    //这个函数用于判断一个字符串是否为数字。它使用了String类的matches()方法，通过正则表达式"\d+"来匹配字符串。如果字符串完全由数字组成，则返回true，否则返回false。
    public boolean isNumeric(String str) {
        return str.matches("\\d+");
    }


    public void getExportListExcel(String title, OutputStream out, List<MediumOnlineExportDTO> onlineExportList,
                                   Map<Integer, OfflineDTO> offlineMap) throws Exception{
        BigDecimal projectTotalPrice = BigDecimal.ZERO;
        //声明一个工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
        //生成一个表格
        HSSFSheet sheet = workbook.createSheet(title);
        //设置表格默认列宽度为15个字符
        sheet.setDefaultColumnWidth(30);
        sheet.setDefaultRowHeight((short) 20);
        sheet.setDefaultRowHeightInPoints(20);
        sheet.setColumnWidth(0, 1500);
        sheet.setColumnWidth(1, 2000);
        //生成一个样式，用来设置标题样式
        HSSFCellStyle style = workbook.createCellStyle();
        //设置这些样式
        HSSFColor hssfColor = setColor(workbook, (byte) 145, (byte) 170, (byte) 223);
        style.setFillForegroundColor(hssfColor.getIndex());
        // 填充效果（全景填充）
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置边框
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        //生成一个字体
        HSSFFont font = workbook.createFont();
        font.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        font.setBold(true);
        font.setFontHeightInPoints((short) 11);
        font.setFontName("微软雅黑");
        //把字体应用到当前的样式
        style.setFont(font);
        // 生成并设置另一个样式,用于设置内容样式
        HSSFCellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
        // 填充效果（全景填充）
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置边框
        headerStyle.setBorderBottom(BorderStyle.THIN);
        headerStyle.setBorderLeft(BorderStyle.THIN);
        headerStyle.setBorderRight(BorderStyle.THIN);
        headerStyle.setBorderTop(BorderStyle.THIN);
        // 生成另一个字体
        HSSFFont font2 = workbook.createFont();
        font2.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        font2.setBold(true);
        font2.setFontHeightInPoints((short) 11);
        font2.setFontName("微软雅黑");
        headerStyle.setFont(font2);

        // 生成价格类型的样式
        HSSFCellStyle priceStyle = workbook.createCellStyle();
        priceStyle.setAlignment(HorizontalAlignment.CENTER);
        priceStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        priceStyle.setBorderBottom(BorderStyle.THIN);
        priceStyle.setBorderLeft(BorderStyle.THIN);
        priceStyle.setBorderRight(BorderStyle.THIN);
        priceStyle.setBorderTop(BorderStyle.THIN);
        HSSFFont priceFont = workbook.createFont();
        priceFont.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        priceFont.setBold(true);
        priceFont.setFontHeightInPoints((short) 11);
        priceFont.setFontName("微软雅黑");
        priceStyle.setFont(priceFont);

        // 生成数量类型的样式
        HSSFCellStyle numberStyle = workbook.createCellStyle();
        numberStyle.setAlignment(HorizontalAlignment.CENTER);
        numberStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        numberStyle.setBorderBottom(BorderStyle.THIN);
        numberStyle.setBorderLeft(BorderStyle.THIN);
        numberStyle.setBorderRight(BorderStyle.THIN);
        numberStyle.setBorderTop(BorderStyle.THIN);
        numberStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
        HSSFFont numberFont = workbook.createFont();
        numberFont.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
//        numberFont.setBold(true);
        numberFont.setFontHeightInPoints((short) 11);
        numberFont.setFontName("微软雅黑");
        numberStyle.setFont(numberFont);

        //生成一个样式，用来设置内容样式
        HSSFCellStyle textStyle = workbook.createCellStyle();
        textStyle.setAlignment(HorizontalAlignment.CENTER);
        textStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        textStyle.setBorderBottom(BorderStyle.THIN);
        textStyle.setBorderLeft(BorderStyle.THIN);
        textStyle.setBorderRight(BorderStyle.THIN);
        textStyle.setBorderTop(BorderStyle.THIN);
        textStyle.setWrapText(true); // 自动换行
        HSSFFont textFont = workbook.createFont();
        textFont.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        textFont.setFontHeightInPoints((short) 11);
        textFont.setFontName("微软雅黑");
        textStyle.setFont(textFont);


        // 加粗样式
        HSSFCellStyle textBoldStyle = workbook.createCellStyle();
        textBoldStyle.setAlignment(HorizontalAlignment.CENTER);
        textBoldStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        textBoldStyle.setBorderBottom(BorderStyle.THIN);
        textBoldStyle.setBorderLeft(BorderStyle.THIN);
        textBoldStyle.setBorderRight(BorderStyle.THIN);
        textBoldStyle.setBorderTop(BorderStyle.THIN);
        textBoldStyle.setWrapText(true); // 自动换行
        HSSFFont textBoldFont = workbook.createFont();
        textBoldFont.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        textBoldFont.setFontHeightInPoints((short) 11);
        textBoldFont.setFontName("微软雅黑");
        textBoldFont.setBold(true);
        textBoldStyle.setFont(textBoldFont);

        //生成一个小计样式
        HSSFCellStyle totalTitleStyle = workbook.createCellStyle();
        totalTitleStyle.setAlignment(HorizontalAlignment.RIGHT);
        totalTitleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        totalTitleStyle.setBorderBottom(BorderStyle.THIN);
        totalTitleStyle.setBorderLeft(BorderStyle.THIN);
        totalTitleStyle.setBorderRight(BorderStyle.THIN);
        totalTitleStyle.setBorderTop(BorderStyle.THIN);
        HSSFFont totalFont = workbook.createFont();
        totalFont.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        totalFont.setBold(true);
        totalFont.setFontHeightInPoints((short) 11);
        totalFont.setFontName("微软雅黑");
        totalTitleStyle.setFont(totalFont);
        HSSFCellStyle totalContentStyle = workbook.createCellStyle();
        totalContentStyle.setAlignment(HorizontalAlignment.CENTER);
        totalContentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        totalContentStyle.setBorderBottom(BorderStyle.THIN);
        totalContentStyle.setBorderLeft(BorderStyle.THIN);
        totalContentStyle.setBorderRight(BorderStyle.THIN);
        totalContentStyle.setBorderTop(BorderStyle.THIN);
        totalContentStyle.setFont(totalFont);
        HSSFCellStyle totalContent2Style = workbook.createCellStyle();
        totalContent2Style.setAlignment(HorizontalAlignment.CENTER);
        totalContent2Style.setVerticalAlignment(VerticalAlignment.CENTER);
        totalContent2Style.setBorderBottom(BorderStyle.THIN);
        totalContent2Style.setBorderLeft(BorderStyle.THIN);
        totalContent2Style.setBorderRight(BorderStyle.THIN);
        totalContent2Style.setBorderTop(BorderStyle.THIN);
        HSSFPalette palette = workbook.getCustomPalette(); //拿到颜色板
        palette.setColorAtIndex(HSSFColor.HSSFColorPredefined.LIME.getIndex(), (byte) 201, (byte) 228, (byte) 180);
        HSSFColor color2 = palette.findColor((byte) 201, (byte) 228, (byte) 180);
        totalContent2Style.setFillForegroundColor(color2.getIndex());
        // 填充效果（全景填充）
        totalContent2Style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        totalContent2Style.setVerticalAlignment(VerticalAlignment.CENTER);
        totalContent2Style.setFont(totalFont);

        //生成一个最终合计样式
        HSSFCellStyle projectTotalTitleStyle = workbook.createCellStyle();
        projectTotalTitleStyle.setAlignment(HorizontalAlignment.RIGHT);
        projectTotalTitleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        projectTotalTitleStyle.setBorderBottom(BorderStyle.THIN);
        projectTotalTitleStyle.setBorderLeft(BorderStyle.THIN);
        projectTotalTitleStyle.setBorderRight(BorderStyle.THIN);
        projectTotalTitleStyle.setBorderTop(BorderStyle.THIN);
        HSSFFont projectTotalFont = workbook.createFont();
        projectTotalFont.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        projectTotalFont.setBold(true);
        projectTotalFont.setFontHeightInPoints((short) 14);
        projectTotalFont.setFontName("微软雅黑");
        projectTotalTitleStyle.setFont(projectTotalFont);
        HSSFCellStyle projectTotalContentStyle = workbook.createCellStyle();
        projectTotalContentStyle.setAlignment(HorizontalAlignment.CENTER);
        projectTotalContentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        projectTotalContentStyle.setBorderBottom(BorderStyle.THIN);
        projectTotalContentStyle.setBorderLeft(BorderStyle.THIN);
        projectTotalContentStyle.setBorderRight(BorderStyle.THIN);
        projectTotalContentStyle.setBorderTop(BorderStyle.THIN);
        HSSFFont projectTotalContentFont = workbook.createFont();
        projectTotalContentFont.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        projectTotalContentFont.setBold(true);
        projectTotalContentFont.setFontHeightInPoints((short) 14);
        projectTotalContentFont.setFontName("微软雅黑");
        projectTotalContentStyle.setFont(projectTotalContentFont);


        //生成一个样式，用来设置注意样式
        HSSFCellStyle steadyContentStyle = workbook.createCellStyle();
        steadyContentStyle.setAlignment(HorizontalAlignment.LEFT);
        steadyContentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        steadyContentStyle.setBorderBottom(BorderStyle.THIN);
        steadyContentStyle.setBorderLeft(BorderStyle.THIN);
        steadyContentStyle.setBorderRight(BorderStyle.THIN);
        steadyContentStyle.setBorderTop(BorderStyle.THIN);
        steadyContentStyle.setWrapText(true); // 自动换行
        HSSFFont steadyContentFont = workbook.createFont();
        steadyContentFont.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        steadyContentFont.setBold(true);
        steadyContentFont.setFontHeightInPoints((short) 11);
        steadyContentFont.setFontName("微软雅黑");
        steadyContentStyle.setFont(steadyContentFont);

        // 固定标题样式
        HSSFCellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        titleStyle.setBorderBottom(BorderStyle.THIN);
        titleStyle.setBorderLeft(BorderStyle.THIN);
        titleStyle.setBorderRight(BorderStyle.THIN);
        titleStyle.setBorderTop(BorderStyle.THIN);
        HSSFFont titleFont = workbook.createFont();
        titleFont.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        titleFont.setBold(true);
        titleFont.setFontHeightInPoints((short) 14);
        titleFont.setFontName("微软雅黑");
        titleStyle.setFont(titleFont);

        Integer rowNum = 1;
        Integer addIndexNum = 1;
        Integer headerSize = 0;
        Integer headerMaxSize = 0;


        rowNum = rowNum + 1;
        HSSFRow titleContentRow = sheet.createRow(rowNum);
        titleContentRow.setHeightInPoints(30);
        CellRangeAddress titleContentRegion = new CellRangeAddress(rowNum, rowNum, 1, 10);
        sheet.addMergedRegion(titleContentRegion);
        RegionUtil.setBorderTop(BorderStyle.THIN, titleContentRegion, sheet);
        RegionUtil.setBorderBottom(BorderStyle.THIN, titleContentRegion, sheet);
        RegionUtil.setBorderLeft(BorderStyle.THIN, titleContentRegion, sheet);
        RegionUtil.setBorderRight(BorderStyle.THIN, titleContentRegion, sheet);
        HSSFCell titleCountCell = titleContentRow.createCell(1);
        titleCountCell.setCellStyle(titleStyle);
        HSSFRichTextString titleCountText = new HSSFRichTextString("资源配包");
        titleCountCell.setCellValue(titleCountText);
        rowNum = rowNum + 1;

        if (!BeanUtil.isNullList(onlineExportList)){

            // 标题
            HSSFRow titleRow = sheet.createRow(rowNum);
            HSSFCell titleCell = titleRow.createCell(1);
            titleRow.setHeightInPoints(20);
            titleCell.setCellStyle(headerStyle);
            HSSFRichTextString titleText = new HSSFRichTextString(addIndexNum + ".线上达人");
            titleCell.setCellValue(titleText);
            addIndexNum = addIndexNum + 1;
            rowNum = rowNum + 1;

            // 获取excel显示第一行的字段名
            List<String> headers = Lists.newArrayList();
            // 生成header
            MediumOnlineExportDTO msg = new MediumOnlineExportDTO();
            Field[] declaredFieldsHeader = msg.getClass().getDeclaredFields();
            for (Field field : declaredFieldsHeader){
                field.setAccessible(true);
                boolean hidden = field.getAnnotation(ApiModelProperty.class).hidden();
                if (!hidden){
                    String value = field.getAnnotation(ApiModelProperty.class).value();
                    headers.add(value);
                }
            }
            headerSize = headers.size();

            //产生表格标题行
            HSSFRow row = sheet.createRow(rowNum);
            row.setHeightInPoints(30);
            // 合并单元格
            CellRangeAddress region = new CellRangeAddress(rowNum - 1, rowNum - 1, 1, headers.size());
            sheet.addMergedRegion(region);
            //添加边框
            RegionUtil.setBorderTop(BorderStyle.THIN, region, sheet);
            RegionUtil.setBorderBottom(BorderStyle.THIN, region, sheet);
            RegionUtil.setBorderLeft(BorderStyle.THIN, region, sheet);
            RegionUtil.setBorderRight(BorderStyle.THIN, region, sheet);
            for (int i = 0; i < headers.size(); i++) {
                HSSFCell cell = row.createCell(i + 1);
                cell.setCellStyle(style);
                HSSFRichTextString text = new HSSFRichTextString(headers.get(i));
                cell.setCellValue(text);
            }

            Integer totalNum = 0;
            BigDecimal totalPrice = BigDecimal.ZERO;
            for (int i = 0; i < onlineExportList.size(); i++) {
                rowNum = rowNum + 1;
                row = sheet.createRow(rowNum);
                int j = 1;
                MediumOnlineExportDTO dto = (MediumOnlineExportDTO) onlineExportList.get(i);
                totalNum = totalNum + dto.getNum();
                totalPrice = totalPrice.add(dto.getTotalPrice());
                dto.setIndexNum(i + 1);
                // 获取excel里对应的值
                Field[] declaredFields = dto.getClass().getDeclaredFields();
                for (int a = 0; a < declaredFields.length; a++) {
                    Field field = declaredFields[a];
                    field.setAccessible(true);
                    Object o = field.get(dto);
                    HSSFCell cell = row.createCell(j++);
                    row.setHeightInPoints(30);
                    cell.setCellStyle(textStyle);

                    if (a == 1) {
                        cell.setCellStyle(textBoldStyle);
                    }
                    if (o == null) {
                        cell.setCellValue("");
                    } else {
                        if (o instanceof BigDecimal) {
                            cell.setCellStyle(priceStyle);
                            if (JSON.toJSONString(o).equals("0E-8")) {
                                cell.setCellValue(0);
                            } else if (JSON.toJSONString(o).equals("0E-24")) {
                                cell.setCellValue(0);
                            } else {
                                cell.setCellValue(((BigDecimal) o).longValue());
                            }
                        } else if (o instanceof Number) {
                            cell.setCellStyle(numberStyle);
                            if (JSON.toJSONString(o).equals("0E-8")) {
                                cell.setCellValue(0);
                            } else if (JSON.toJSONString(o).equals("0E-24")) {
                                cell.setCellValue(0);
                            } else {
                                cell.setCellValue(((Number) o).longValue());
                            }
                        } else {
                            cell.setCellValue(o.toString());
                            if (isNumeric(o.toString())){
                                String string = o.toString();
                                cell.setCellStyle(numberStyle);
                                if (JSON.toJSONString(o).equals("0E-8")) {
                                    cell.setCellValue(0);
                                } else if (JSON.toJSONString(o).equals("0E-24")) {
                                    cell.setCellValue(0);
                                } else {
                                    BigDecimal bigDecimal = new BigDecimal(string);
                                    cell.setCellValue(bigDecimal.longValue());
                                }
                            }
                        }
                        if (field.getName().equals("indexNum")){
                            cell.setCellStyle(textStyle);
                            cell.setCellValue(i + 1);
                        }
                    }
                }
            }
            // 合计
            rowNum = rowNum + 1;
            HSSFRow totalCountRow = sheet.createRow(rowNum);
            totalCountRow.setHeightInPoints(30);
            CellRangeAddress totalCountRegion = new CellRangeAddress(rowNum, rowNum, 1, headers.size() - 2);
            sheet.addMergedRegion(totalCountRegion);
            //添加边框
            RegionUtil.setBorderTop(BorderStyle.THIN, totalCountRegion, sheet);
            RegionUtil.setBorderBottom(BorderStyle.THIN, totalCountRegion, sheet);
            RegionUtil.setBorderLeft(BorderStyle.THIN, totalCountRegion, sheet);
            RegionUtil.setBorderRight(BorderStyle.THIN, totalCountRegion, sheet);
            HSSFCell totalCountCell = totalCountRow.createCell(1);
            totalCountCell.setCellStyle(totalTitleStyle);
            HSSFRichTextString totalCountText = new HSSFRichTextString("合计：");
            totalCountCell.setCellValue(totalCountText);
            // 数量合计
            HSSFCell totalCountNumCell = totalCountRow.createCell(headers.size() - 1);
            totalCountNumCell.setCellStyle(totalContentStyle);
            if (JSON.toJSONString(totalNum).equals("0E-8")){
                totalCountNumCell.setCellValue(0);
            }else if (JSON.toJSONString(totalNum).equals("0E-24")){
                totalCountNumCell.setCellValue(0);
            }else {
                totalCountNumCell.setCellValue(((Number) totalNum).longValue());
            }
            // 价格合计
            HSSFCell totalCountPriceCell = totalCountRow.createCell(headers.size());
            totalCountPriceCell.setCellStyle(totalContentStyle);
            if (JSON.toJSONString(totalPrice).equals("0E-8")){
                totalCountPriceCell.setCellValue(0);
            }else if (JSON.toJSONString(totalPrice).equals("0E-24")){
                totalCountPriceCell.setCellValue(0);
            }else {
                totalCountPriceCell.setCellValue(((Number) totalPrice).longValue());
            }
            projectTotalPrice = projectTotalPrice.add(totalPrice);

        } else {
            rowNum = rowNum - 1;
        }
        if (offlineMap != null){
            for (OfflineDTO offlineAllExportDTO : offlineMap.values()){

                // 标题
                rowNum = rowNum + 1;
                HSSFRow titleRow = sheet.createRow(rowNum);
                titleRow.setHeightInPoints(20);
                HSSFCell titleCell = titleRow.createCell(1);
                titleCell.setCellStyle(headerStyle);
                HSSFRichTextString titleText = new HSSFRichTextString(addIndexNum + "." + offlineAllExportDTO.getExcelShowTitle());
                titleCell.setCellValue(titleText);
                addIndexNum = addIndexNum + 1;

                // 获取excel显示第一行的字段名
                List<String> headers = Lists.newArrayList();

                String steadyContent = offlineAllExportDTO.getSteadyContent(); // 注意点
                Map<String, Integer> headerRegionMap = new HashMap<>();
                if (!BeanUtil.isNullList(offlineAllExportDTO.getList())){
                    BigDecimal totalPrice = BigDecimal.ZERO;
                    // 生成header
                    Field[] declaredFieldsHeader = offlineAllExportDTO.getObjectClass().getDeclaredFields();
                    for (Field field : declaredFieldsHeader){
                        field.setAccessible(true);
                        boolean hidden = field.getAnnotation(ApiModelProperty.class).hidden();
                        if (!hidden){
                            String value = field.getAnnotation(ApiModelProperty.class).value();
                            boolean required = field.getAnnotation(ApiModelProperty.class).required();
                            if (StringUtil.isNotBlank(value) && !value.equals("steadyContent") && !required){
                                int position = field.getAnnotation(ApiModelProperty.class).position();
                                headerRegionMap.put(value, position == 0 ? 1 : position);
                                headers.add(value);
                            }
                        }
                    }
                    headerSize = headers.size();
                    // 合并单元格
                    headerMaxSize = headers.size() - 1 < 10 ? 10 : (headers.size() - 1);
                    CellRangeAddress region = new CellRangeAddress(rowNum, rowNum, 1, headerMaxSize);
                    sheet.addMergedRegion(region);
                    //添加边框
                    RegionUtil.setBorderTop(BorderStyle.THIN, region, sheet);
                    RegionUtil.setBorderBottom(BorderStyle.THIN, region, sheet);
                    RegionUtil.setBorderLeft(BorderStyle.THIN, region, sheet);
                    RegionUtil.setBorderRight(BorderStyle.THIN, region, sheet);

                    //产生表格标题行
                    rowNum = rowNum + 1;
                    HSSFRow row = sheet.createRow(rowNum);
                    row.setHeightInPoints(30);
                    int zIndex = 0;
                    for (int i = 0; i < headers.size() - 1; i++) {
                        Integer i1 = headerRegionMap.get(headers.get(i));
                        if (i1 != null) {
                            for (int j = 0; j < i1; j++){
                                zIndex = zIndex + j;
                                HSSFCell cell = row.createCell(i + 1 + j + zIndex);
                                cell.setCellStyle(style);
                                HSSFRichTextString text = new HSSFRichTextString(headers.get(i));
                                cell.setCellValue(text);
                            }
                        }else {
                            HSSFCell cell = row.createCell(i + 1 + zIndex);
                            cell.setCellStyle(style);
                            HSSFRichTextString text = new HSSFRichTextString(headers.get(i));
                            cell.setCellValue(text);
                        }
                    }
                    for (int i = 0; i < headers.size() - 1; i++) {
                        // 合并单元格
                        Integer i1 = headerRegionMap.get(headers.get(i));
                        if (i1 != null && i1 > 1) {
                            CellRangeAddress region1 = new CellRangeAddress(rowNum, rowNum, i + 1, i + i1);
                            sheet.addMergedRegion(region1);
                            RegionUtil.setBorderTop(BorderStyle.THIN, region1, sheet);
                            RegionUtil.setBorderBottom(BorderStyle.THIN, region1, sheet);
                            RegionUtil.setBorderLeft(BorderStyle.THIN, region1, sheet);
                            RegionUtil.setBorderRight(BorderStyle.THIN, region1, sheet);
                        }
                    }

                    for (int i = 0; i < offlineAllExportDTO.getList().size(); i++){
                        rowNum = rowNum + 1;
                        row = sheet.createRow(rowNum);

                        Object dto = offlineAllExportDTO.getList().get(i);
                        // 获取excel里对应的值
                        Field[] declaredFields = dto.getClass().getDeclaredFields();
                        int yIndex = 0;
                        for (int a = 0; a < declaredFields.length; a++) {
                            Field field = declaredFields[a];
                            String value = field.getAnnotation(ApiModelProperty.class).value();
                            field.setAccessible(true);
                            boolean hidden = field.getAnnotation(ApiModelProperty.class).hidden();
                            if (!hidden){
                                Object o = field.get(dto);
                                Integer i1 = headerRegionMap.get(value);
                                Integer iIndex = i1;
                                if (iIndex == null) {
                                    iIndex = 1;
                                }
                                for (int j1 = 0; j1 < iIndex; j1++){
                                    yIndex = yIndex + j1;
                                    HSSFCell cell = row.createCell(a + yIndex + j1);
                                    row.setHeightInPoints(30);
                                    cell.setCellStyle(textStyle);
                                    if (a == 1){
                                        cell.setCellStyle(textBoldStyle);
                                    }
                                    if (o == null){
                                        cell.setCellValue("");
                                    }else {
                                        if (o instanceof BigDecimal){
                                            if (field.getName().equals("totalPrice")){
                                                totalPrice = totalPrice.add(new BigDecimal(o.toString()));
                                            }
                                            cell.setCellStyle(priceStyle);
                                            if (JSON.toJSONString(o).equals("0E-8")){
                                                cell.setCellValue(0);
                                            }else if (JSON.toJSONString(o).equals("0E-24")){
                                                cell.setCellValue(0);
                                            }else {
                                                cell.setCellValue(((BigDecimal) o).longValue());
                                            }
                                        }else if (o instanceof Number){
                                            cell.setCellStyle(numberStyle);
                                            if (JSON.toJSONString(o).equals("0E-8")){
                                                cell.setCellValue(0);
                                            }else if (JSON.toJSONString(o).equals("0E-24")){
                                                cell.setCellValue(0);
                                            }else {
                                                cell.setCellValue(((Number) o).longValue());
                                            }
                                        }else {
                                            String v = o.toString();
                                            if (o.toString().indexOf("\n") != -1){
                                                v = v.replace("\n", "");
                                            }
                                            cell.setCellValue(v);
                                            if (isNumeric(o.toString())){
                                                String string = o.toString();
                                                cell.setCellStyle(numberStyle);
                                                if (JSON.toJSONString(o).equals("0E-8")) {
                                                    cell.setCellValue(0);
                                                } else if (JSON.toJSONString(o).equals("0E-24")) {
                                                    cell.setCellValue(0);
                                                } else {
                                                    BigDecimal bigDecimal = new BigDecimal(string);
                                                    cell.setCellValue(bigDecimal.longValue());
                                                }
                                            }
                                        }
                                        if (field.getName().equals("indexNum")){
                                            cell.setCellStyle(textStyle);
                                            cell.setCellValue(i + 1);
                                        }
                                        if (field.getName().equals("pic")){
                                            cell.setCellValue("");
                                            if (StringUtil.isNotBlank(o.toString())){
                                                int size = headers.size();
                                                String reference = field.getAnnotation(ApiModelProperty.class).reference();
                                                if (reference != null && reference.equals("show2")){
                                                    size = size + 1;
                                                }
                                                drawPictureInfoExcel(workbook, row, o.toString(), sheet, rowNum, 11);
                                            }else {
                                                cell.setCellStyle(null);
                                            }
                                        }
                                    }
                                }
                            }
                        }

                        for (int a = 0; a < declaredFields.length; a++){
                            int yIndex1 = 0;
                            Field field = declaredFields[a];
                            // 合并单元格
                            String value = field.getAnnotation(ApiModelProperty.class).value();
                            Integer i1 = headerRegionMap.get(value);
                            Integer iIndex = i1;
                            if (iIndex == null) {
                                iIndex = 1;
                            }
                            if(iIndex > 1){
                                yIndex1 = yIndex1 + iIndex;
                                CellRangeAddress region1 = new CellRangeAddress(rowNum, rowNum, a, a + yIndex1 - 1);
                                sheet.addMergedRegion(region1);
                                RegionUtil.setBorderTop(BorderStyle.THIN, region1, sheet);
                                RegionUtil.setBorderBottom(BorderStyle.THIN, region1, sheet);
                                RegionUtil.setBorderLeft(BorderStyle.THIN, region1, sheet);
                                RegionUtil.setBorderRight(BorderStyle.THIN, region1, sheet);
                            }
                        }

                    }


                    // 合计
                    rowNum = rowNum + 1;
                    HSSFRow totalCountRow = sheet.createRow(rowNum);
                    totalCountRow.setHeightInPoints(30);
                    CellRangeAddress totalCountRegion = new CellRangeAddress(rowNum, rowNum, 1, headerMaxSize - 1);
                    sheet.addMergedRegion(totalCountRegion);
                    //添加边框
                    RegionUtil.setBorderTop(BorderStyle.THIN, totalCountRegion, sheet);
                    RegionUtil.setBorderBottom(BorderStyle.THIN, totalCountRegion, sheet);
                    RegionUtil.setBorderLeft(BorderStyle.THIN, totalCountRegion, sheet);
                    RegionUtil.setBorderRight(BorderStyle.THIN, totalCountRegion, sheet);

                    HSSFCell totalCountCell = totalCountRow.createCell(1);
                    totalCountCell.setCellStyle(totalTitleStyle);
                    HSSFRichTextString totalCountText = new HSSFRichTextString("小计：");
                    totalCountCell.setCellValue(totalCountText);
                    // 价格合计
                    HSSFCell totalCountPriceCell = totalCountRow.createCell(headerMaxSize);

                    totalCountPriceCell.setCellStyle(totalContent2Style);
                    if (JSON.toJSONString(totalPrice).equals("0E-8")){
                        totalCountPriceCell.setCellValue(0);
                    }else if (JSON.toJSONString(totalPrice).equals("0E-24")){
                        totalCountPriceCell.setCellValue(0);
                    }else {
                        totalCountPriceCell.setCellValue(((Number) totalPrice).longValue());
                    }
                    projectTotalPrice = projectTotalPrice.add(totalPrice);


                    // 注意点
                    if (StringUtil.isNotBlank(steadyContent)){
                        rowNum = rowNum + 1;
                        HSSFRow steadyContentRow = sheet.createRow(rowNum);
                        steadyContentRow.setHeightInPoints(100);
                        CellRangeAddress steadyContentRegion = new CellRangeAddress(rowNum, rowNum, 1, headerMaxSize);
                        sheet.addMergedRegion(steadyContentRegion);
                        RegionUtil.setBorderTop(BorderStyle.THIN, steadyContentRegion, sheet);
                        RegionUtil.setBorderBottom(BorderStyle.THIN, steadyContentRegion, sheet);
                        RegionUtil.setBorderLeft(BorderStyle.THIN, steadyContentRegion, sheet);
                        RegionUtil.setBorderRight(BorderStyle.THIN, steadyContentRegion, sheet);
                        HSSFCell steadyContentCell = steadyContentRow.createCell(1);
                        steadyContentCell.setCellStyle(steadyContentStyle);
                        HSSFRichTextString steadyContentText = new HSSFRichTextString(steadyContent);
                        steadyContentCell.setCellValue(steadyContentText);
                    }

                }
            }
        }

        // 货值总计
        rowNum = rowNum + 1;
        HSSFRow projectTotalCountRow = sheet.createRow(rowNum);
        projectTotalCountRow.setHeightInPoints(50);
        CellRangeAddress totalCountRegion = new CellRangeAddress(rowNum, rowNum, 1, headerMaxSize - 1);
        sheet.addMergedRegion(totalCountRegion);
        //添加边框
        RegionUtil.setBorderTop(BorderStyle.THIN, totalCountRegion, sheet);
        RegionUtil.setBorderBottom(BorderStyle.THIN, totalCountRegion, sheet);
        RegionUtil.setBorderLeft(BorderStyle.THIN, totalCountRegion, sheet);
        RegionUtil.setBorderRight(BorderStyle.THIN, totalCountRegion, sheet);
        HSSFCell projectTotalCountCell = projectTotalCountRow.createCell(1);
        projectTotalTitleStyle.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
        projectTotalTitleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        projectTotalCountCell.setCellStyle(projectTotalTitleStyle);
        HSSFRichTextString projectTotalCountText = new HSSFRichTextString("货值总计：");
        projectTotalCountCell.setCellValue(projectTotalCountText);
//        // 价格合计
        HSSFCell projectTotalCountPriceCell = projectTotalCountRow.createCell(headerMaxSize);
        projectTotalContentStyle.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
        projectTotalContentStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        projectTotalCountPriceCell.setCellStyle(projectTotalContentStyle);
        if (JSON.toJSONString(projectTotalPrice).equals("0E-8")){
            projectTotalCountPriceCell.setCellValue(0);
        }else if (JSON.toJSONString(projectTotalPrice).equals("0E-24")){
            projectTotalCountPriceCell.setCellValue(0);
        }else {
            projectTotalCountPriceCell.setCellValue(((Number) projectTotalPrice).longValue());
        }

        try {
            workbook.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    public OfflineDTO getOfflineDTO(ProjectMediumListDTO dto, BigDecimal oneWeekPrice){
        List<Object> objects = new ArrayList<>();
        OfflineDTO offlineDTO = new OfflineDTO();
        // 判断各种类型
        if (dto.getMediumOffline().getExcelShowTitleId() == 519){
            // 出租车后窗静态投影
            OfflineDTOByTaxi dto1 = new OfflineDTOByTaxi(dto.getMediumOffline().getId(), 1, dto.getMediumOffline().getAddress(), dto.getMediumOffline().getPublicationSeconds(), dto.getMediumOffline().getOneDayOperationTime(), dto.getMediumOffline().getPredictDayPersonNum(), dto.getMediumOffline().getGoodPrice(), dto.getNum(), oneWeekPrice, dto.getMediumOffline().getPic());
            objects.add(dto1);
            offlineDTO.setObjectClass(OfflineDTOByTaxi.class);
        } else if (dto.getMediumOffline().getExcelShowTitleId() == 520) {
            // 梯媒-电子屏
            OfflineDTOByTMDZP dto1 = new OfflineDTOByTMDZP(dto.getMediumOffline().getId(), 1, dto.getMediumOffline().getAddress(), dto.getMediumOffline().getBroadcastFrequency(), dto.getMediumOffline().getGoodPrice(), dto.getMediumOffline().getPredictDayPersonNum(), dto.getNum(), dto.getMediumOffline().getScreenNum(), oneWeekPrice, dto.getMediumOffline().getPic());
            objects.add(dto1);
            offlineDTO.setObjectClass(OfflineDTOByTMDZP.class);
        } else if (dto.getMediumOffline().getExcelShowTitleId() == 521){
            // 梯媒-电子屏-新潮
            OfflineDTOByTMDZPXC dto1 = new OfflineDTOByTMDZPXC(dto.getMediumOffline().getId(), 1, dto.getMediumOffline().getAddress(), dto.getMediumOffline().getPutType(), dto.getMediumOffline().getBroadcastFrequency(), dto.getMediumOffline().getGoodPrice(), dto.getMediumOffline().getPredictDayPersonNum(), dto.getNum(), dto.getMediumOffline().getScreenNum(), oneWeekPrice, dto.getMediumOffline().getPic());
            objects.add(dto1);
            offlineDTO.setObjectClass(OfflineDTOByTMDZPXC.class);
        } else if (dto.getMediumOffline().getExcelShowTitleId() == 522){
            // 梯媒-框架
            OfflineDTOByTMKJ dto1 = new OfflineDTOByTMKJ(dto.getMediumOffline().getId(), 1, dto.getMediumOffline().getAddress(), dto.getMediumOffline().getPutType(), dto.getMediumOffline().getRemark(), dto.getMediumOffline().getGoodPrice(), dto.getMediumOffline().getPredictDayPersonNum(), dto.getNum(), dto.getMediumOffline().getScreenNum(), oneWeekPrice, dto.getMediumOffline().getPic());
            objects.add(dto1);
            offlineDTO.setObjectClass(OfflineDTOByTMKJ.class);
        } else if (dto.getMediumOffline().getExcelShowTitleId() == 523){
            // 社区门禁-电子屏
            OfflineDTOBySQMJDZP dto1 = new OfflineDTOBySQMJDZP(dto.getMediumOffline().getId(), 1, dto.getMediumOffline().getAddress(), dto.getMediumOffline().getPublicationSeconds(), dto.getMediumOffline().getBroadcastFrequency(), dto.getMediumOffline().getGoodPrice(), dto.getMediumOffline().getPredictDayPersonNum(), dto.getNum(), dto.getMediumOffline().getScreenNum(), oneWeekPrice, dto.getMediumOffline().getPic());
            objects.add(dto1);
            offlineDTO.setObjectClass(OfflineDTOBySQMJDZP.class);
        } else if (dto.getMediumOffline().getExcelShowTitleId() == 524){
            //超市价签屏
            OfflineDTOByCSJQP dto1 = new OfflineDTOByCSJQP(dto.getMediumOffline().getId(), 1, dto.getMediumOffline().getAddress(), dto.getMediumOffline().getPublicationSeconds(), dto.getMediumOffline().getBroadcastFrequency(), dto.getMediumOffline().getGoodPrice(), dto.getMediumOffline().getPredictDayPersonNum(), dto.getNum(), dto.getMediumOffline().getScreenNum(), oneWeekPrice, dto.getMediumOffline().getPic());
            objects.add(dto1);
            offlineDTO.setObjectClass(OfflineDTOByCSJQP.class);
        } else if (dto.getMediumOffline().getExcelShowTitleId() == 525){
            // 自助收银屏
            OfflineDTOByZZSYP dto1 = new OfflineDTOByZZSYP(dto.getMediumOffline().getId(), 1, dto.getMediumOffline().getAddress(), dto.getMediumOffline().getScreenPosition(), dto.getMediumOffline().getBroadcastFrequency(), dto.getMediumOffline().getGoodPrice(), dto.getMediumOffline().getPredictDayPersonNum(), dto.getNum(), dto.getMediumOffline().getScreenNum(), oneWeekPrice, dto.getMediumOffline().getPic());
            objects.add(dto1);
            offlineDTO.setObjectClass(OfflineDTOByZZSYP.class);
        } else if (dto.getMediumOffline().getExcelShowTitleId() == 494){
            // 影院一
            OfflineDTOByYY1 dto1 = new OfflineDTOByYY1(dto.getMediumOffline().getId(), 1, dto.getMediumOffline().getAddress(), dto.getMediumOffline().getScreenNum(), dto.getMediumOffline().getPublicationSeconds(), dto.getMediumOffline().getGoodPrice(), dto.getMediumOffline().getPredictDayPersonNum(), dto.getNum(), dto.getMediumOffline().getOneDayOperationTime(), oneWeekPrice, dto.getMediumOffline().getPic());
            objects.add(dto1);
            offlineDTO.setObjectClass(OfflineDTOByYY1.class);
        } else if (dto.getMediumOffline().getExcelShowTitleId() == 527){
            // 影院二
            OfflineDTOByYY2 dto1 = new OfflineDTOByYY2(dto.getMediumOffline().getId(), 1, dto.getMediumOffline().getAddress(), dto.getMediumOffline().getScreenNum(), dto.getMediumOffline().getPublicationSeconds(), dto.getMediumOffline().getGoodPrice(), dto.getMediumOffline().getPredictDayPersonNum(), dto.getNum(), dto.getMediumOffline().getOneDayOperationTime(), oneWeekPrice, dto.getMediumOffline().getPic());
            objects.add(dto1);
            offlineDTO.setObjectClass(OfflineDTOByYY2.class);
        } else if (dto.getMediumOffline().getExcelShowTitleId() == 528){
            // 影院三
            OfflineDTOByYY3 dto1 = new OfflineDTOByYY3(dto.getMediumOffline().getId(), 1, dto.getMediumOffline().getAddress(), dto.getMediumOffline().getScreenNum(), dto.getMediumOffline().getPublicationSeconds(), dto.getMediumOffline().getGoodPrice(), dto.getMediumOffline().getPredictDayPersonNum(), dto.getNum(), dto.getMediumOffline().getOneDayOperationTime(), oneWeekPrice, dto.getMediumOffline().getPic());
            objects.add(dto1);
            offlineDTO.setObjectClass(OfflineDTOByYY3.class);
        } else if (dto.getMediumOffline().getExcelShowTitleId() == 526){
            // 映前广告
            OfflineDTOByYQGG dto1 = new OfflineDTOByYQGG(dto.getMediumOffline().getId(), 1, dto.getMediumOffline().getAddress(), dto.getMediumOffline().getScreenPosition(), dto.getMediumOffline().getPublicationSeconds(), dto.getMediumOffline().getGoodPrice(), dto.getNum(), dto.getMediumOffline().getPredictDayPersonNum(), dto.getMediumOffline().getScreenNum(), oneWeekPrice, dto.getMediumOffline().getPic());
            objects.add(dto1);
            offlineDTO.setObjectClass(OfflineDTOByYQGG.class);
        } else if (dto.getMediumOffline().getExcelShowTitleId() == 529){
            // 映前广告-万达影院
            OfflineDTOByYQGGWDYY dto1 = new OfflineDTOByYQGGWDYY(dto.getMediumOffline().getId(), 1, dto.getMediumOffline().getAddress(), dto.getMediumOffline().getScreenPosition(), dto.getMediumOffline().getPublicationSeconds(), dto.getMediumOffline().getGoodPrice(), dto.getMediumOffline().getPredictDayPersonNum(), dto.getNum(), dto.getMediumOffline().getScreenNum(), oneWeekPrice, dto.getMediumOffline().getPic());
            objects.add(dto1);
            offlineDTO.setObjectClass(OfflineDTOByYQGGWDYY.class);
        } else if (dto.getMediumOffline().getExcelShowTitleId() == 530){
            // UC浏览器 外渠神马搜索
            OfflineDTOByUC dto1 = new OfflineDTOByUC(dto.getMediumOffline().getId(), 1, dto.getMediumOffline().getAddress(), dto.getMediumOffline().getName(), dto.getMediumOffline().getPredictDayPersonNum(), dto.getMediumOffline().getShowTypeContent(), dto.getMediumOffline().getPositionSpecs(), dto.getMediumOffline().getRemark(), oneWeekPrice, dto.getMediumOffline().getPic());
            objects.add(dto1);
            offlineDTO.setObjectClass(OfflineDTOByUC.class);
        } else if (dto.getMediumOffline().getExcelShowTitleId() == 496){
            // 高铁-外采
            OfflineDTOByGTWC dto1 = new OfflineDTOByGTWC(dto.getMediumOffline().getId(), 1, dto.getMediumOffline().getCity(), dto.getMediumOffline().getName(), dto.getMediumOffline().getPositionSpecs(), dto.getMediumOffline().getPublicationSeconds(), dto.getMediumOffline().getPredictDayPersonNum(), dto.getNum(), dto.getMediumOffline().getScreenNum(), oneWeekPrice, dto.getMediumOffline().getPic());
            objects.add(dto1);
            offlineDTO.setObjectClass(OfflineDTOByGTWC.class);
        } else if (dto.getMediumOffline().getExcelShowTitleId() == 534){
            // 高铁-自营
            OfflineDTOByGTZY dto1 = new OfflineDTOByGTZY(dto.getMediumOffline().getId(), 1, dto.getMediumOffline().getName(), dto.getMediumOffline().getPositionSpecs(), dto.getMediumOffline().getPublicationSeconds(), dto.getMediumOffline().getScreenNum(), dto.getMediumOffline().getPredictDayPersonNum(), dto.getMediumOffline().getBroadcastFrequency(), dto.getMediumOffline().getGoodPrice(), dto.getNum(), oneWeekPrice, dto.getMediumOffline().getPic());
            objects.add(dto1);
            offlineDTO.setObjectClass(OfflineDTOByGTZY.class);
        } else if (dto.getMediumOffline().getExcelShowTitleId() == 531){
            // app
            OfflineDTOByAPP dto1 = new OfflineDTOByAPP(dto.getMediumOffline().getId(), 1, dto.getMediumOffline().getAddress(), dto.getMediumOffline().getName(), dto.getMediumOffline().getShowTypeContent(), dto.getMediumOffline().getScreenPosition(), dto.getMediumOffline().getPredictDayPersonNum(), dto.getNum(), oneWeekPrice, dto.getMediumOffline().getPic());
            objects.add(dto1);
            offlineDTO.setObjectClass(OfflineDTOByAPP.class);
        } else if (dto.getMediumOffline().getExcelShowTitleId() == 532){
            // 机场智能手推车广告
            OfflineDTOByJCZNSGG dto1 = new OfflineDTOByJCZNSGG(dto.getMediumOffline().getId(), 1, dto.getMediumOffline().getCity(), dto.getMediumOffline().getName(), dto.getMediumOffline().getShowTypeContent(), dto.getMediumOffline().getPublicationSeconds(), dto.getMediumOffline().getPredictDayPersonNum(), dto.getNum(), dto.getMediumOffline().getScreenNum(), oneWeekPrice, dto.getMediumOffline().getPic());
            objects.add(dto1);
            offlineDTO.setObjectClass(OfflineDTOByJCZNSGG.class);
        } else if (dto.getMediumOffline().getExcelShowTitleId() == 495){
            // 商场超极屏
            OfflineDTOBySCCJP dto1 = new OfflineDTOBySCCJP(dto.getMediumOffline().getId(), 1, dto.getMediumOffline().getCity(), dto.getMediumOffline().getName(), dto.getMediumOffline().getScreenPosition(), dto.getMediumOffline().getMarketNum(), dto.getMediumOffline().getPredictDayPersonNum(), dto.getMediumOffline().getBroadcastFrequency(), dto.getMediumOffline().getShowTypeContent(), dto.getNum(), oneWeekPrice, dto.getMediumOffline().getPic());
            objects.add(dto1);
            offlineDTO.setObjectClass(OfflineDTOBySCCJP.class);
        } else if (dto.getMediumOffline().getExcelShowTitleId() == 497){
            // 机场
            OfflineDTOByJC dto1 = new OfflineDTOByJC(dto.getMediumOffline().getId(), 1, dto.getMediumOffline().getName(), dto.getMediumOffline().getPositionSpecs(), dto.getMediumOffline().getPublicationSeconds(), dto.getMediumOffline().getScreenNum(), dto.getMediumOffline().getPredictDayPersonNum(), dto.getMediumOffline().getBroadcastFrequency(), dto.getMediumOffline().getGoodPrice(), dto.getNum(), oneWeekPrice, dto.getMediumOffline().getPic());
            objects.add(dto1);
            offlineDTO.setObjectClass(OfflineDTOByJC.class);
        } else if (dto.getMediumOffline().getExcelShowTitleId() == 533){
            // 地铁便民信息屏*套餐一
            OfflineDTOByDTBMXXP1 dto1 = new OfflineDTOByDTBMXXP1(dto.getMediumOffline().getId(), 1, dto.getMediumOffline().getCity(), dto.getMediumOffline().getPositionSpecs(), dto.getMediumOffline().getPublicationSeconds(), dto.getMediumOffline().getScreenNum(), dto.getMediumOffline().getPredictDayPersonNum(), dto.getMediumOffline().getBroadcastFrequency(), dto.getMediumOffline().getGoodPrice(), dto.getNum(), oneWeekPrice, dto.getMediumOffline().getPic());
            objects.add(dto1);
            offlineDTO.setObjectClass(OfflineDTOByDTBMXXP1.class);
        } else if (dto.getMediumOffline().getExcelShowTitleId() == 535){
            // 地铁便民信息屏*套餐二
            OfflineDTOByDTBMXXP2 dto1 = new OfflineDTOByDTBMXXP2(dto.getMediumOffline().getId(), 1, dto.getMediumOffline().getCity(), dto.getMediumOffline().getPositionSpecs(), dto.getMediumOffline().getPublicationSeconds(), dto.getMediumOffline().getScreenNum(), dto.getMediumOffline().getPredictDayPersonNum(), dto.getMediumOffline().getBroadcastFrequency(), dto.getMediumOffline().getGoodPrice(), dto.getNum(), oneWeekPrice, dto.getMediumOffline().getPic());
            objects.add(dto1);
            offlineDTO.setObjectClass(OfflineDTOByDTBMXXP2.class);
        } else if (dto.getMediumOffline().getExcelShowTitleId() == 498){
            // 户外大屏
            OfflineDTOByHWDP dto1 = new OfflineDTOByHWDP(dto.getMediumOffline().getId(), 1, dto.getMediumOffline().getCity(), dto.getMediumOffline().getPositionSpecs(), dto.getMediumOffline().getOneDayOperationTime(), dto.getMediumOffline().getPublicationSeconds(), dto.getMediumOffline().getPredictDayPersonNum(), dto.getMediumOffline().getGoodPrice(), dto.getNum(), oneWeekPrice, dto.getMediumOffline().getPic());
            objects.add(dto1);
            offlineDTO.setObjectClass(OfflineDTOByHWDP.class);
        }
        offlineDTO.setList(objects);
        return offlineDTO;
    }

}
